Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Use PowerShell to Determine the number DML operations in SQL Server Scripts


By:   |   Last Updated: 2016-09-15   |   Comments (2)   |   Related Tips: More > 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

Review the output from the PowerShell script

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:

PowerShell output including the number of Inserts, Updates, Deletes, Truncates and Drops

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++ } }
PowerShell output including the number of Inserts, Updates, Deletes, Truncates and 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


Last Updated: 2016-09-15


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, September 16, 2016 - 7:57:42 AM - Tim Back To Top

Jeff - you are exactly correct, which is why I cautioned users on the first point in Next Steps.  We've all seen developers get creative; in addition, while this may be useful for one-time CRUD ops in production as summaries, I would not use this for other operations, like migrations or ETL.  Excellent point and readers should definitely consider my caution on the first next step.


Thursday, September 15, 2016 - 1:25:45 PM - jeff_yao Back To Top

 

Hi Tim,

You are trying to create a t-sql script parser by using PS, but this is far from accurate, a few cases here, 1st, if you have nested comment with /* ... /* ..*/ ...*/, or you have some weird comments such as

--- this is comment /*

block comment */

You will have error too.

But the most important defficiency is to deal with the new t-sql MERGE statement which can contain INTO or INSERT / DELETE / UPDATE keywords.

Another issue is that when we have column name like [Update_Date] or [Insert_By] etc, your current approach will fail as well.

To be honest, I once tried to do the same thing (with PS and RegEx), but the error rate is too high to be useable (in my environment at least) due to all the issues I mentioned above, so what I end up is to use SQL Server's own parser utility microsoft.sqlserver.transactsql.scriptdom (with c#)

I just want to point these out so readers can be aware of the potential issues, but I have to say this script may be helpful to some not complex and more standardized environments.

 

Regards,

Jeff_Yao


Learn more about SQL Server tools