Sql-server – How to compare Schemas for Multiple Databases to a True Copy

database-designpowershellsql serversql-server-2012ssdt

We have a database for each customer, over 700 databases. All the schemas are supposed to be exact same.
We have Source Control database, and want to schema compare to the 700. Sometimes schema in Production get out of line/ drift away from actual Source Control Master.

How do I compare schemas from multiple databases to a Visual Studio SSDT Project, or a gold copy empty Database in Production?

I looked at various techniques, which may not be optimal.

Compare to SSDT Project

This person utilizes a recursive script: Schema Compare multiple databases at once

However author utilizes file size, which I do not like as difference indicator.

'My technique for comparing file sizes is to open a CMD Window by using Shift-RightClick on the folder they are in, choose ‘Open Command Window Here’, then run a ‘DIR’, which will list the files with the size in bytes. '

Extract the Gold .dacpac:
sqlpackage.exe /a:Extract /scs:Server=MyLaptopSQL2014;Database=Test; /tf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac 

 Compare to the target databases:  
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test1 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest1.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test2 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest2.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test3 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest3.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test4 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest4.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True 
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test5 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest5.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True

Compare to Gold Copy Database In Production:

This technique doesn't look at default indicators or index column names, triggers, stored procedures., etc

Different Ways to Compare SQL Server Tables Schema and Data

So what is the optimal way to compare schemas to many databases? I do not need to know the results, I just need a Flag indicator at end of day, Different Y/N.
It would be nice to use a DMV method and display yes/no results in a simple table, but SSDT will work also.

Best Answer

This will compare database scripts. Did not want to utilize sql compare, as it may take additional schema lock time on server.

#Set-ExecutionPolicy RemoteSigned


#OriginalDatabaseScript, this can be generate from source control or Master Copy database in Production
$OriginalScript = 'C:\DatabaseCompare\OriginalScript.sql'

#Source: Place in test file below, list databases by running select * from sys.databases where database_id > 4
$DatabaseSourceList = 'C:\DatabaseCompare\DatabaseList.txt'

#Destination: Database Files Generate Scripts
$Filepath='C:\DatabaseCompare\scripts' # local directory to save build-scripts to


foreach($line in Get-Content $DatabaseSourceList) 
{

$DataSource='localhost' # server name and instance
$Database=$line # the database to copy from
# set "Option Explicit" to catch subtle errors
set-psdebug -strict
$ErrorActionPreference = "stop" # you can opt to stagger on, bleeding, if an error occurs
# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
$ms='Microsoft.SqlServer'
$v = [System.Reflection.Assembly]::LoadWithPartialName( "$ms.SMO")
if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {
[System.Reflection.Assembly]::LoadWithPartialName("$ms.SMOExtended") | out-null
   }
$My="$ms.Management.Smo" #
$s = new-object ("$My.Server") $DataSource
if ($s.Version -eq  $null ){Throw "Can't find the instance $Datasource"}
$db= $s.Databases[$Database] 
if ($db.name -ne $Database){Throw "Can't find the database '$Database' in $Datasource"};
$transfer = new-object ("$My.Transfer") $db
$CreationScriptOptions = new-object ("$My.ScriptingOptions") 
$CreationScriptOptions.ExtendedProperties= $true # yes, we want these
$CreationScriptOptions.DRIAll= $true # and all the constraints 
$CreationScriptOptions.Indexes= $true # Yup, these would be nice
$CreationScriptOptions.Triggers= $true # This should be included when scripting a database
$CreationScriptOptions.ScriptBatchTerminator = $true # this only goes to the file
$CreationScriptOptions.IncludeHeaders = $false; # of course
$CreationScriptOptions.ToFileOnly = $true #no need of string output as well
$CreationScriptOptions.IncludeIfNotExists = $true # not necessary but it means the script can be more versatile
$CreationScriptOptions.Filename =  "$($FilePath)\$($Database)_Build.sql"; 
$NewFilename =  "$($FilePath)\$($Database)_Build2.sql"; 

$transfer = new-object ("$My.Transfer") $s.Databases[$Database]

$transfer.options=$CreationScriptOptions # tell the transfer object of our preferences
$transfer.ScriptTransfer()
"Created Database " + $Database

#Get-Content $CreationScriptOptions.Filename | Where { $_ -notmatch "/" } | Set-Content $NewFilename



if(Compare-Object -ReferenceObject $(Get-Content $OriginalScript) -DifferenceObject $(Get-Content $NewFilename))
    {$Database + " Files are different"}
Else 
    {$Database + "Files are the same"}


 }

Edited from here: Automated Script-generation with Powershell and SMO

Related Question