Dumping SQL Server Tables to CSV Files (Part 2)

Published on Thursday, September 17, 2015

Photo by Jan Loyde Cabrera on Unsplash

There's more than one way to do it isn't just a Perl thing; just about every automation task you can imagine can be done in more ways than you can imagine. I just posted a script the other day to dump tables from a SQL Server database into CSV files using PowerShell and the venerable sqlcmd Utility. One of the challenges is getting the various parameters for sqlcmd passed in properly from PowerShell, and I did it using an array of arguments and some awkward string escaping.

Today I learned that PowerShell already has the Invoke-Sqlcmd cmdlet, which is designed to (mostly) replace sqlcmd. By using Invoke-Sqlcmd, we can bypass all the argument array ugliness and write a simpler script:

$tableQuery = @"
SELECT sobjects.name 
FROM sysobjects sobjects 
WHERE sobjects.xtype = 'U' 

$loc = Get-Location

$server = ".\SQLEXPRESS"
$db = "[dbname]"

$tables = Invoke-Sqlcmd -ServerInstance $ser`ver -Database $db -Query $tableQuery

$tables | select -ExpandProperty name | select @{Name="filename";Expression={Join-Path -Path $loc -ChildPath "$_.csv"}}, @{Name="query";Expression={"SET NOCOUNT ON`nSelect * From $_"}} | % {
    Invoke-Sqlcmd -ServerInstance $server -Database $db -Query $_.query | Export-Csv $_.filename -NoTypeInformation

Set-Location $loc

This is much more readable. The only part that might not be immediately obvious is the use of the $loc variable for storing the current location when we start the script.

The reason for this is that Invoke-Sqlcmd takes advantage of PowerShell's ability to treat everything kind of like a file system. PowerShell can change context to work within things like the registry as easily as it can change to different drive volumes (e.g., open PowerShell and try cd HCKU:, then try dir, then be very, very careful). In the same way, it can also work within SQL Server. Using Invoke-Sqlcmd will change the context to be the SQL Server instance, which is super convenient if you're doing a lot of SQL Server stuff.

But here we want to dump all the tables to files in the folder where we ran the script. So we need to keep track of that location (using Get-Location) and make sure that we have an absolute path for the output location of Export-Csv (using Join-Path). And, at the end, we want to make sure to return to our original location instead of hanging around in SQL Server space.

Even with that little bit of overhead, Invoke-Sqlcmd is a much cleaner and easier way of performing this kind of task.