Recently, while cleaning up a database, I found myself doing a lot of manual searching in some code to see if stored procedures existed. Like any good Computer Scientist, automation came to mind. Who really wants to manually complete a task that can be automated? Then came my recent superhero, PowerShell, to the rescue.

First, was to check to see if it could be done. Sure, it seemed obvious enough (at least to me) that it could be. Otherwise PowerShell was about to loose it's name. Having worked enough with it previously the real question was, Can PowerShell search an entire directory recursively? After working with the cmdlet Select-String for a little bit it seemed that it alone wasn't going to get the job done. However, never fear, Get-Childitem came in and saved the day.

Finally, once a proof of concept was complete, the rest of the script needed to be formulated to take more than one input. There would be no point to automating this if it didn't take multiple inputs and search on them. In the beginning searching code for stored procedures was the object, however another trait of a good computer scientist it to know when to make something generic enough to make it reusable. If you read one of my previous posts you know that you can compile PowerShell scripts together and make your own library of functions. Why not do that here?

$outFile = "Z:\PowerShell\FileSearch\results.sql"
$workingFile = "Z:\PowerShell\FileSearch\file.txt"
$codePath = "C:\Projects\SearchingProject\*"

$fileContents = get-content $workingFile
$count = $fileContents | Measure-Object
$counter = 1
$sqlDrop = ""

$startTime = Get-Date
foreach ($storedProcedure in $fileContents)
{
    $results = Get-Childitem $codePath -recurse | Select-String -pattern $storedProcedure -List

    if($results -eq $null)
    {
        $sqlDrop += "DROP PROCEDURE [" + $storedProcedure + "]`n"
    }
    
    write-host $counter " of " $count.count
    $counter++
}
$endTime = Get-Date

$sqlDrop += "/*`n"
$sqlDrop += "* Start Time: " + $startTime + "`n"
$sqlDrop += "* End Time: " + $endTime + "`n"
$sqlDrop += "* Total Time: " + ($endTime - $startTime) + "`n*/"
$sqlDrop | Out-File $outFile


As a side note, this script was specified so it would output the text and file type needed to complete this task. For good measure I added a beginning and ending time to the searching. It's always nice to see how long something takes. I'm a metrics kinda guy. Until next time, think about your driving habits.