By: Tim Smith | Updated: 2016-09-15 | Comments (2) | Related: > PowerShell
Problem
We have INSERT, UPDATE and DELETE scripts that we run in production and we'd like to create summaries of these scripts, telling us what the script contains, so that we can look at a report summary to reduce the overhead of looking through each script to identify which script may have caused an issue if there is an issue related to new data, changed data or missing data. Is there a way to do this in PowerShell?
Solution
We can use one of my favorite libraries to do this easily - .NET's very fast StreamReader, for reading a SQL file. Likewise, since PowerShell uses .NET, this library includes many useful built in function for strings such as Substring, IndexOf, Replace, Contains, and ToLower, which we'll be using in this tip. Most do exactly what their name sounds like.
First, let's handle single line and multi-line comments:
Function Read-File {
Param(
[string]$file
)
Process
{
$readfile = New-Object System.IO.StreamReader($file)
[int]$lineno
[int]$startskipping = 0
while (($line = $readfile.ReadLine()) -ne $null)
{
$lineno++
if ($line.IndexOf("--") -ne -1)
{
### Remove all characters after the symbols --
$line = $line.Replace($line.Substring($line.IndexOf("--"),($line.Length - $line.IndexOf("--"))),"")
}
elseif ($line.IndexOf("/*") -ne -1)
{
### Find the ending */ and skip all lines between them
$startskipping = 1
### What happens if a multi-line comment is really only one line?
if ($line.IndexOf("*/") -ne -1)
{
$startskipping = 0
}
$line = ""
}
elseif ($line.IndexOf("*/") -ne -1)
{
$line = ""
### We found the last line of the multi-line comment, stop skipping
$startskipping = 0
}
elseif ($startskipping -eq 1)
{
$line = ""
}
$line
}
$readfile.Close()
$readfile.Dispose()
}
}
Read-File -file "C:\files\script.sql"
Let's look at what we did here: on a single line comment, we want to skip the part of the line after the --. We don't want to skip before that if it's on a later part of the line. In a similar manner, we want to skip all the multi-line comments and some developers (yours truly) will do these on one line, which is why we have an extra handling of an opening multi-line commenting tag where we see if there's a closing multi-line commenting tag on the same line.
Let's look at an example using the below T-SQL script, running the script in ISE, and evaluating the output:
/* ---- Saved in a SQL File Created by John Doe August 10th, 2012 */ -- First we create a temp table SELECT * INTO ##tblOurTempTable FROM tblOurTable -- Update status one records for report UPDATE tblTableTwo SET Notes = 'Outstanding' WHERE Status = 1 -- Remove status 0 DELETE FROM tblTableThree WHERE Status = 0 SELECT * -- Result will be everything; below is filtered out just for an ID check --, t.ID FROM ##tblOurTempTable t INNER JOIN tblTableTwo tt ON t.ID = tt.ID AND tt.EINIDF = 1 INNER JOIN tblTableThree ttt ON t.ID = ttt.ID AND ttt.Amount = t.Amount -- Remove temp table DROP TABLE ##tblOurTempTable
We'll note that the multi-line comment is gone, as are the one-line comments and the comment after the *. Next, we're going to use counters for the statements we're looking for in the script:
Function Read-File {
Param(
[string]$file
)
Process
{
$readfile = New-Object System.IO.StreamReader($file)
[int]$lineno
[int]$startskipping = 0
### Count commands:
[int]$inserts = 0
[int]$updates = 0
[int]$deletes = 0
[int]$truncates = 0
[int]$drops = 0
while (($line = $readfile.ReadLine()) -ne $null)
{
$lineno++
if ($line.IndexOf("--") -ne -1)
{
### Remove all characters after the symbols --
$line = $line.Replace($line.Substring($line.IndexOf("--"),($line.Length - $line.IndexOf("--"))),"")
}
elseif ($line.IndexOf("/*") -ne -1)
{
### Find the ending */ and skip all lines between them
$startskipping = 1
### What happens if a multi-line comment is really only one line?
if ($line.IndexOf("*/") -ne -1)
{
$startskipping = 0
}
$line = ""
}
elseif ($line.IndexOf("*/") -ne -1)
{
$line = ""
### We found the last line of the multi-line comment, stop skipping
$startskipping = 0
}
elseif ($startskipping -eq 1)
{
$line = ""
}
if ($line.ToLower().Trim().Contains("into") -eq $true) { $inserts++ }
if ($line.ToLower().Trim().Contains("update") -eq $true) { $updates++ }
if ($line.ToLower().Trim().Contains("delete") -eq $true) { $deletes++ }
if ($line.ToLower().Trim().Contains("truncate") -eq $true) { $truncates++ }
if ($line.ToLower().Trim().Contains("drop") -eq $true) { $drops++ }
$line
}
$result = "$file - Inserts: $inserts; Updates: $updates; Deletes: $deletes; Truncates: $truncates; Drops: $drops"
Write-Warning $result
$readfile.Close()
$readfile.Dispose()
}
}
Read-File -file "C:\files\script.sql"
In my case, I wrote out the warning of the changes, but I could change this to wrap in HTML and email, or save as a text file. As we see below, we have 1 insert, 1 update, 1 delete and 1 drop:
Keep in mind that both of the below code snippets are valid ways to insert data:
INSERT INTO tblOurTable SELECT * FROM tblOurOtherTable
SELECT * INTO tblOurTable FROM tblOurOtherTable
In addition, depending on how developers code, they may put these on multiple lines or the same line. In the same way, some developers will actually write an INSERT, UPDATE or DELETE on one line, while putting the table on the next line - which limits using spaces following the keywords. Be careful about doing this or assuming this. We also see that even if someone inserts multiple rows, or deletes multiple rows, this summary will tell us whether the script has these CRUD functions in them - with some production rollouts, we may only be looking for a script that has a failed update. Finally, if I wanted to filter out drops of temp tables, I could change the drop if statement line to (and output below the code):
if ($line.ToLower().Trim().Contains("drop") -eq $true) { if (!$line.ToLower().Trim().Contains("drop table #")) { $drops++ } }
You must know your developers here and I prefer over-assuming that under-assuming because the latter may miss something, even if the former over reports on occasion.
Next Steps
- I highly suggest looking through DML scripts before using or updating the above code to fit your environment. Developers do many different things and we have to be careful what assumptions we make.
- The above script can be expanded if your DML scripts include other code than INSERTs, UPDATEs and DELETEs. For an example, you can search for ##Global temp tables by looking for double hashes.
- Check out these other PowerShell tips
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2016-09-15
