Skip to main content

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.

## SQL Server Code Comparison With PowerShell
# 
## 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.
# 
## https://www.mssqltips.com/sqlservertip/4310/simple-tsql-code-comparison-with-powershell/

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
    }
}

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><strong><em>").Replace("</td>","</em></strong></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"