## 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 = "

" $column1 = $sqlobject1 | ForEach-Object {$x = 1} { New-Object PSObject -Property @{ Line = $x;Html = "" }; $x++ } $column2 = $sqlobject2 | ForEach-Object {$x = 1} { New-Object PSObject -Property @{ Line = $x;Html = "" }; $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("","") } else { $columnhighlight += $highlight2.Html } $begin++ } $tableone = $htmlbody + "" $tabletwo = $tableone + "
Existing T-SQLNew T-SQL
" + $x + "" + $_ + "
" + $x + "" + $_ + "
","").Replace("" + $column1.Html + "
Line NumberTSQL:
" + $columnhighlight + "
Line NumberTSQL:

" $finalresult = $filepath + $name + ".html" $tabletwo | Out-File $finalresult -Force } } Compare-Objects -filepath "C:\Files\" -name "stpCompare"