Use Powershell’s Select-String to find SQL update statements

I had a SQL script and I wanted to check if there are any update statements in the file. It was a seemingly easy task but after messing around with Powershell’s Select-String, I learnt a few gotchas that are worthy of a write-up.

Let’s start with something simple:

SQL:

update dbo.table set column = 1

Powershell:

Get-ChildItem script.sql | Select-String -Pattern "update"

Output:

script.sql:1:update dbo.table set column = 1

Now, let’s say I want to exclude print statements and comments that have a random “update” in them.

SQL:

-- start to update dbo.table...
update dbo.table set column = 1
print 'update complete.'

Without changing the powershell, I will get all three lines returned. It is reasonable to assume that a legit update statement would always come with a set clause, so maybe I can include that in the search pattern.

Powershell:

Get-ChildItem script.sql | Select-String -Pattern "update.*set.*="

Output:

script.sql:2:update dbo.table set column = 1

This looks good so far. However, it is also reasonable to assume that most update statements would have the set clause on the next line, let’s see how that’s handled.

SQL:

-- start to update dbo.table...
update dbo.table 
set column = 1
print 'update complete.'

Oops. Nothing returned this time. I learnt this in hindsight that Get-ChildItem is feeding into Select-String one line at a time so it will never find the update.*set pattern. In order to get around this problem, I just need to feed the entire script into Select-String as a string.

Powershell:

[string](Get-Content script.sql) | Select-String -Pattern "update.*set.*="

Output:

-- start to update dbo.table... update dbo.table  set column = 1 print 'update complete.'

It worked a treat!

–oo00oo–
For more info, please refer to the links below:
Select-String