PowerShell and SQL Server – Use a Text File to Drive A Script

Happy Thanksgiving! If you’re in the US, we give one day a year (although I’m thankful year-round) to giving thanks for all our blessings.

Today I thought I might share a little technique I use to perform an action across a set of objects, using a text file to change the objects. Here’s the standard way to open and read through a text file in the PowerShell environment:


  "c:\temp\databases.txt") | foreach {write-Host "Do Something Here with " $_ }

All this does is open a text file called "databases.txt", in which I have a list of a few of the databases on my server. It then uses a loop (the foreach part) to write out to the screen the words "Do something here with" and then a special variable – the $_ part. Since I used the "pipe" symbol | to send the contents of the text file from the left, the $_ means "current line". The foreach loops and voila – there you have it!

Now, to make it do something useful, you can change the write-Host part. Using the SQL Server PowerShell provider for SQL Server 2008 (this also works against 2K5 and 2K systems as well, as long as you have the provider installed), you can change the $machineName and $instanceName below to the name of your server and instance to list out all of the database tables:


"c:\temp\databases.txt" | foreach {CD sqlserver:\sql\$machineName\$instanceName\databases\$_\tables; write-Host "Database Name: " $_ ; DIR | MORE;}