Recently I came across an opportunity to automate a manual extracting process: export a number of tables from a database using CSV format with headers. After some researching (googling), typing and debugging, a PowerShell script is ready to go. This script uses the SQL Server Management Objects to extract data from a SQL Server database and exports the results into CSV files using the PowerShell build-in function export-csv. It’s easy to read and maintain:

$ErrorActionPreference="Stop"
if ($args.Count -le 2)
{
    throw "Usage: <server name> <db name> <script file>"
}
$servername = $args[0]
$database = $args[1]
$sourcefile = $args[2]
[system.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$server = New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($servername)
$1 = Get-Item($sourcefile)
$ds = $server.Databases[$database].ExecuteWithResults("$(Echo $1.OpenText().ReadToEnd())")
$cnt = 0
Foreach ($dt in $ds.Tables)
{
    $cnt ++
    Write-Host "Writing table: "$cnt
    $file = ".\" + $cnt.ToString() + ".csv"
    $dt | export-csv $file -notypeinformation
    Write-Host "Completed!"
}

This script takes 3 parameters: server name, database name and script file.

The first parameter <server name> includes both the server name and the instance name. For example, it will be .\SQL2012 when connecting to instance SQL2012 on the local server.

The second parameter <Database name> is the database that the script uses.

The third parameter <Script File> is a .sql file which contains all the queries required to run. The script could contain more than one query, separated by “GO”. The results will be saved into separated CSV files with numbered filenames accordingly.

Below is one example of the source script file - Master.sql:

select *
 from sys.configurations
go
select *
 from sys.master_files
go

The CSV files will be saved to the current folder. It’s a good idea to have a dedicated folder for this purpose. Let’s use it:

.\csv.ps1 .\SQL2012 master master.sql

This command tells SQL Server to run master.sql in master database in SQL2012 instance on local server. As a result, 2 files are generated: 1.csv and 2.csv. They are the results of first query (select * from objects) and second query (select * from sys.tables) accordingly.

If you have the .csv file extension associated with Excel, you will be able to view them directly in Office Excel. Below are 2 screenshots of viewing .csv files in Excel:

Enjoy!