Recursive Find and Replace Your SQL Files with PowerShell

This is just a quick blog to help out with something I saw discussed on twitter yesterday.

When I do my presentations I have a set of scripts that have the name of the computer and instance I am working with.  I’m not a fan of using localhost or anything like that; I also like to use instance names that let people know what version of SQL that I’m using.  When I switch computers I spend less than a minute changing all of the names.  At the PASS Summit I used a laptop that I had just purchased; here’s the script I used to rename everything.

foreach ($SC in dir "$home\Documents\PoSh\" -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
(Get-Content $SC) | Foreach-Object { $_ -replace 'KILIMANJARO', 'R2' } | Set-Content $SC
}

To make this work for you just change out the highlighted parts above to whatever you need.  The part that says “$home\Documents\PoSh\” will go to the PoSh folder under your “My Documents” directory (If you don’t have one, now’s a good time ).  You can also use a path like C:\SQL\Databases\AdventureQuirks\ here.  The -replace ‘KILIMANJARO’, ‘R2’ portion seems pretty self explanatory; same goes for -recurse.

If you just wanted to search for all the files with a certain table name, column name, stored proc name, etc… and return a report (not to modify) you can use something like this:

#Make sure to navigate to the directory that you want to start looking in:
## 
cd c:\temp            
foreach ($SC in dir -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}

This piece of code is setup slightly different solely to demonstrate another way you can use this functionality.  Make sure that you navigate to directory that you want to search in first for this script.  When you get the results you may end up seeing the same filename listed more that once because the “pattern” appears in the file multiple times.  To make the results show each filename just once simple add -List.

One final option that I want to call out is -Filter.  If you want to search for only .SQL files in a directory (or .PS1, .txt, whatever) just add this: -Filter *.SQL

So you might end up using something like this:

foreach ($SC in dir C:\SQL\Databases\AdventureQuirks\ -recurse -Filter *.SQL | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}

Alright I better stop here before I start showing off some other features that I just learned.
Hope that helps!