Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Simple T-SQL Code Comparison With PowerShell


By:   |   Last Updated: 2016-07-15   |   Comments (5)   |   Related Tips: More > T-SQL

Problem

We would like to automate a simple compare of objects (stored procedures, views, functions, etc.) that we plan to change on SQL Server with the old and new object set side by side. Is there a way to do this and automate future changes in PowerShell?

Solution

Using the SQL management objects library and some PowerShell techniques, we can do a simple stack of two objects side-by-side, one which is the scripted object that existed before the change and the other is the object that the old object was changed to.

For organization purposes, let's first focus on scripting the existing object and saving it to a file.

Function Return-Object {
    Param(
        [ValidateLength(3,200)][string]$server
        , [ValidateLength(3,200)][string]$database
        , [ValidateLength(3,200)][string]$objects
        , [ValidateLength(3,300)][string]$name
        , [ValidateLength(3,500)][string]$outfilepath
        , [ValidateSet("2008R2","2012","2014","2016")][string]$version
    )
    Process
    {
        $nl = [Environment]::NewLine

        switch ($version)
        {
            "2008R2" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2012" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2014" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\120\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }

            "2016" { 
                Write-Host "Adding libraries for version $version"
                Add-Type -Path "C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
            }
        }

        $sqlsrv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
        $outfile = $outfilepath + $name + "1.sql"
        $sqlsrv.Databases["$database"].$objects["$name"].Script() | Out-File $outfile
    }
}

In the case of the above script, we return the script of the object that we pass in and note that we can enter both the object type ($objects) and the object name ($name). We also will see that the returned file will have a 1 attached to it. This is designed so that we can save our original object, such as view_MyView as view_MyView.sql while the scripted object will have a one attached to the name, such as view_MyView1.sql. Since we'll be changing the object from an original file, this file of the old object will allow us to see each object.

Let's create two stored procedures that we'll be comparing:

CREATE PROCEDURE stpCompare
AS
BEGIN

 SELECT @@SERVERNAME

END
CREATE PROCEDURE stpCompare
AS
BEGIN

 ---- Returns the server name
 SELECT @@SERVERNAME

END

The first procedure (without the comment) is the procedure that is live on the server and will be the procedure that we call our function Return-Object. The second procedure is the new procedure and we'll have it saved in the same directory, but without the 1 attached to its name, so as an example in this case, C:\Files\stpCompare.sql and C:\Files\stpCompare1.sql. In our simple comparison, we will want to compare these files together line-by-line and output an HTML file that shows the comparisons stacked next to each other.

Function Compare-Objects {
    Param(
        [string]$filepath
        , [string]$name
    )
    Process
    {
        $sqlfileone = $filepath + $name + "1.sql"
        $sqlfiletwo = $filepath + $name + ".sql"
        
        $sqlobject1 = Get-Content $sqlfileone
        $sqlobject2 = Get-Content $sqlfiletwo

        $htmlbody = "<html><head><title></title></head><body><p><table><tr><th>Existing T-SQL</th><th>New T-SQL</th></tr>"

        $column1 = $sqlobject1 | ForEach-Object {$x = 1} { New-Object PSObject -Property @{ Line = $x;Html = "<tr><td>" + $x + "</td><td>" + $_ + "</td></tr>" }; $x++ }
        $column2 = $sqlobject2 | ForEach-Object {$x = 1} { New-Object PSObject -Property @{ Line = $x;Html = "<tr><td>" + $x + "</td><td>" + $_ + "</td></tr>" }; $x++ }

        $columnhighlight = ""
        $lengthloop = $column2.Length
        $begin = 1

        while ($begin -le $lengthloop)
        {
            $highlight1 = $column1 | Where-Object {$_.Line -eq $begin} | Select-Object Html
            $highlight2 = $column2 | Where-Object {$_.Line -eq $begin} | Select-Object Html

            ### If logic if lines are the same or not
            if ($highlight1.Html -ne $highlight2.Html)
            {
                $columnhighlight += ($highlight2.Html).Replace("<td>","<td><b><i>").Replace("</td>","</i></b></td>")
            }
            else
            {
                $columnhighlight += $highlight2.Html
            }
            $begin++
        }

        $tableone = $htmlbody + "<td><table><tr><th>Line Number</th><th>TSQL:</th></tr>" + $column1.Html + "</table></td>"
        $tabletwo = $tableone + "<td><table><tr><th>Line Number</th><th>TSQL:</th></tr>" + $columnhighlight + "</table></td></table></p></body></html>"

        $finalresult = $filepath + $name + ".html"
        $tabletwo | Out-File $finalresult -Force
    }
}

Compare-Objects -filepath "C:\Files\" -name "stpCompare"

And our output when we call this with the other function:

The above function loops through each line of the files and numbers the lines in one column of the HTML table while adding another column with the actual line of code, then saves that to an HTML table with the code and line numbers stacked side-by-side. What this function does not do is continue to go loop through the lines and measure if there is a line that exists on one file that is also present on another file, for an example if line 8 in the existing procedure had SELECT *, but in the new procedure that line was on line 10. This does highlight the differences overall using bold and italics and is useful for change summaries, especially if you're wanting a receipt of changes that were made, or if you're wanting to attach the HTML documents and have them emailed to the teams and managers making or monitoring the changes.

Next Steps
  • Consider that with source control, changes are tracked through the history. For an example, if we're using GitHub, we can click the history selection on an online repository and view past changes. In many cases though, source control may not be manager friendly, so providing a summary like the above in case of an issue can help.
  • The script is object independent as far as views, procedures and tables are concerned.
  • This is very useful for change and deployment accountability and summaries.


Last Updated: 2016-07-15


get scripts

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




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, July 29, 2016 - 5:22:45 AM - Tim Back To Top

@mblixt Excellent; added to GitHub with you as a contributor.  If you're on there, send me your username and I can you to a repo for this.


Saturday, July 23, 2016 - 6:13:07 PM - mblixt Back To Top

very nice idea! I liked it so much I added some functionality like date and comparison to last written output on the fly without any access to an SQL to test against. (the idea was to have an scheduled job running this and only saving an output if it is was changed from last run..)

since I will very likely use this or something based upon this I post my drafts here. (once again - untested code against SQL so beware of bugs...)

Function Return-MSSQLObject{
    # from https://www.mssqltips.com/sqlservertip/4310/simple-tsql-code-comparison-with-powershell/
    # added some error handling and such
    [cmdletbinding()]
    Param(
        [ValidateLength(3,200)][string]$server,
        [ValidateLength(3,200)][string]$database,
        [ValidateLength(3,200)][string]$objects,
        [ValidateLength(3,300)][string]$name,
        [ValidateLength(3,500)][string]$outfilepath = 'c:\sqlcompareobjects\',
        [ValidateSet('2008R2','2012','2014','2016')][string]$version
    )
    Begin{
        $nl = [Environment]::NewLine
        $criticalerror = $false
        $versioncode = ('100','110','120','130')
        $versionumber = ('2008R2','2012','2014','2016')
    }
    Process{
        if(!$version){
            #if the user did not specify a version
            $prepath = 'C:\Program Files (x86)\Microsoft SQL Server\'
            $postpath = '\SDK\Assemblies\Microsoft.SqlServer.Smo.dll'

            $foundversions = @()
            $versioncode | ForEach-Object {
                if(Test-Path ($prepath+$_+$postpath)){
                    Write-Host 'found an library'
                    $foundversions += $versioncode
                }
          
            }
            if(!$foundversions){
                Write-Warning ('no libraries at all for SQL found under path {0} - aborting execution' -f $prepath)
                $criticalerror = $true
            }
            elseif($foundversions.Count -gt 1){
                Write-Warning 'found more than one SQL installation - specify which one to use with the ''version'' parameter'
                $criticalerror = $true
            }
            else{
                $version = $versionumber[$versioncode.indexof($foundversions[0])]
                Write-Host ('Adding libraries for SQL version {0}' -f $version)
                Add-Type -Path ($prepath+$foundversions[0]+$postpath)
            }
        }
        else{
            $path = $prepath+$versioncode[$versionumber.IndexOf($version)]+$postpath
            if(Test-Path $path){
                Write-Host ('Adding libraries for SQL version {0}' -f $version)
                Add-Type -Path $path
            }
            else{
                Write-Warning ('Cannot find path {0} - aborting execution' -f $path)
                $criticalerror = $true
            }
        }
    }
    End{
        if(!$criticalerror){
            $now = [datetime]::Now.Tostring('yyyyMMdd_HHmm') # ISO standard form of date time
            $sqlsrv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
            $outfile = $outfilepath + $name + '_' + $now + '.sql'
            $newitem = $sqlsrv.Databases["$database"].$objects["$name"].Script()
            $previousitempath = Get-ChildItem -Path $outfilepath | Where-Object {$_.Fullname -match ($name+'_\d{8}_\d{4}')} | Sort-Object {$_.LastWriteTime} -Descending |  Select-Object -first 1 -ExpandProperty FullName
            if(!$previousitempath){
                $create_a_new_item = $true
            }
            else{
                $previousitem = Get-Content $previousitempath
                $newitem | Out-File $env:TEMP+'\'+$name
                $currentitem = Get-Content -Path ($env:TEMP+'\'+$name)
                if((Compare-Object -ReferenceObject $previousitem -DifferenceObject $currentitem | Select-Object SideIndicator -ExpandProperty SideIndicator).count -gt 0){
                    $create_a_new_item = $true
                    Remove-Item -Path $env:TEMP+'\'+$name -Force
                }else{
                    Write-Host ('New and old object is the same - no need to write a new one')
                }
            }

            if($create_a_new_item){
                $newitem | Out-File $outfile
            }
        }
    }
} # END Return-MSSQLObject

Function Compare-MSSQLObjects{
    <#
            .SYNOPSIS
            try to compare the latest two files given its exported name
            .DESCRIPTION
            taken from https://www.mssqltips.com/sqlservertip/4310/simple-tsql-code-comparison-with-powershell/
            full credit of the code goes to Tim Smith, just expanded his code a bit
            .PARAMETER Path
            The path which the files will reside in
            .PARAMETER Name
            The base name of the files to look for
            .EXAMPLE
            Compare-MSSQLObjects -Path 'C:\Files\' -Name 'stpCompare'
            will look into the folder c:\files and try to find the two latest written files which fit the regex pattern stpCompare_\d{8}_\d{4}
            if not two files is found an error will occur and execution will be terminated, otherwise the files will be loaded into memory and converter to HTML and line for line
            compared, if they differ they will show up as BOLD in the right hand column output will be saved as an html file in the same folder as specified in Path
    #>
    [cmdletbinding()]
    Param(
        [string]$Path= 'c:\sqlcompareobjects\',
        [Parameter(Mandatory=$true)][string]$Name
    )
    Begin{
        $lastfiles = Get-ChildItem -Path $Path | Where-Object {$_.Fullname -match ($Name+'_\d{8}_\d{4}')} | Sort-Object {$_.LastWriteTime} -Descending |  Select-Object -first 2 -ExpandProperty FullName
        if($lastfiles.count -ne 2){
            Write-Warning ('Sorry there is not two files in path {0} with the name {1} so comparison is impossible' -f ($Path,$Name))
            BREAK
        }
        else{
            $newestfile = $lastfiles[0]
            $almostnewestfile = $lastfiles[1]
        }

    }
    Process
    {
        $sqlobject2 = Get-Content $newestfile
        $sqlobject1 = Get-Content $almostnewestfile

        $dummy = $newestfile -match '\d{8}_\d{4}'
        $newestdate = $Matches[0]
        $dummy = $almostnewestfile -match '\d{8}_\d{4}'
        $almostnewestdate = $Matches[0]


        $htmlbody = '<html><head><title></title></head><body><p><table><tr><th>Existing T-SQL from '+$almostnewestdate+'</th><th>New T-SQL from '+$newestdate+'</th></tr>'

        $column1 = $sqlobject1 | ForEach-Object {$x = 1} { New-Object PSObject -Property @{ Line = $x;Html = '<tr><td>' + $x + '</td><td>' + $_ + '</td></tr>' }; $x++ }
        $column2 = $sqlobject2 | ForEach-Object {$x = 1} { New-Object PSObject -Property @{ Line = $x;Html = '<tr><td>' + $x + '</td><td>' + $_ + '</td></tr>' }; $x++ }

        $columnhighlight = ''
        $lengthloop = $column2.Length
        $begin = 1

        while ($begin -le $lengthloop)
        {
            $highlight1 = $column1 | Where-Object {$_.Line -eq $begin} | Select-Object Html
            $highlight2 = $column2 | Where-Object {$_.Line -eq $begin} | Select-Object Html

            ### If logic if lines are the same or not
            if ($highlight1.Html -ne $highlight2.Html)
            {
                $columnhighlight += ($highlight2.Html).Replace('<td>','<td><b><i>').Replace('</td>','</i></b></td>')
            }
            else
            {
                $columnhighlight += $highlight2.Html
            }
            $begin++
        }

        $tableone = $htmlbody + '<td><table><tr><th>Line Number</th><th>TSQL:</th></tr>' + $column1.Html + '</table></td>'
        $tabletwo = $tableone + '<td><table><tr><th>Line Number</th><th>TSQL:</th></tr>' + $columnhighlight + '</table></td></table></p></body></html>'

        $finalresult = $path + $Name +'_comparing_outputs_'+$almostnewestdate+'_and_'+$newestdate + '.html'
        $tabletwo | Out-File $finalresult -Force
    }
} #END Compare-MSSQLObjects


Tuesday, July 19, 2016 - 8:44:29 AM - Tim Back To Top

@Sujoy:

The error on System.Data.SqlClient indicates that the connection is invalid, or was terminated.


Monday, July 18, 2016 - 10:58:01 AM - Sujoy Back To Top

 

 

 Exception: System.Data.SqlClient.SqlException (0x80131904): A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)


Friday, July 15, 2016 - 10:30:44 AM - Wanderlei Santos Back To Top

Great script, but it would be nice if we could report differences side-by-side like a unix 'diff -y' command. Compare-object is similar, but not side-by-side, and has other issues.

 


Learn more about SQL Server tools