Executing all .SQL files in a directory with Powershell

Some time ago I published a blog post on how to execute all files in a directory against a given database. This took a single line on the command prompt, today I saw an implementation of this in T-SQL which had 86 lines of code.

As I’ve recently been looking at improving my Powershell skills, I thought it would be a good idea to try an produce a solution using that, and here is what I came up with:

foreach ($f in Get-ChildItem -path "C:\temp\SQLScripts\" -Filter *.sql | sort-object -desc )
{
$out = "C:\temp\SQLScripts\OUTPUT\" + $f.name.split(".")[0] + ".txt" ;
invoke-sqlcmd -InputFile $f.fullname | format-table | out-file -filePath $out
}
 

This may not be the most elegant of solutions, but here is what it does:

Get-Childitem will return all the files in the "C:\temp\SQLScripts\" directory, using the *.sql filter it will only get files with that extension (just in case there are other files which we don’t want to execute!).

Piping this through a sort will mean I do not have to execute the files in alphabetical order if the sort order can be identified through one of the file’s attributes e.g the order in which the files were created or as in the example, descending alphabetical file name order.

Then for each file I construct the name output the file where I want the output to go, the split function in conjunction with the 0 index takes the left part of the sql script’s filename up to the first dot (.).

I then use the cmdlet invoke-sqlcmd to execute the script. The above code assumes that you are in the context of the database where you wish the script the be run e.g

SQLSERVER:\SQL\ComputerName\InstanceName\Databases\DatabaseName

but you may want to specify the server, database, username and password parameters if this is not the case. It would be very easy to run all the scripts against every database on a given instance or a specific database on all instances of SQL Server on the given computer. This for me, is one of the attractions of Powershell, as trying to find a solution in T-SQL or at the command prompt will be much harder.