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.
