PoshCode Logo PowerShell Code Repository

Compare-DatabaseSchema (modification of post by view diff)
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/865"></script>download | new post

Compare the tables (and columns with the -Column parameter) in two databases, checking for differences.

  1. param( $SqlServerOne = 'YourDatabaseServer',
  2.         $FirstDatabase = 'FirstDatabaseToCompare',
  3.         $SqlUsernameOne = 'SQL Login',
  4.         $SqlPasswordOne = 'SQL Password',
  5.         $SqlServerTwo = 'YourDatabaseServer',
  6.         $SecondDatabase = 'SecondDatabaseToCompare',
  7.         $SqlUsernameTwo = 'SQL Login',
  8.         $SqlPasswordTwo = 'SQL Password',
  9.         $FilePrefix = 'Log',
  10.         [switch]$Log,
  11.         [switch]$Column)
  12.  
  13. $File = $FilePrefix + '{0}-{1}.csv'
  14.  
  15. $TableQuery = @"
  16. select sysobjects.name as TableName
  17. from sysobjects
  18. where sysobjects.xtype like 'U' and --specify only user tables
  19. sysobjects.name not like 'dtproperties' --specify only user tables
  20. "@
  21.  
  22. function Run-Query()
  23. {
  24.         param (
  25.         $SqlQuery,
  26.         $SqlServer,
  27.         $SqlCatalog,
  28.         $SqlUser,
  29.         $SqlPass
  30.         )
  31.        
  32.         $SqlConnString = "Server = $SqlServer; Database = $SqlCatalog; user = $SqlUser; password = $SqlPass"
  33.         $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  34.         $SqlConnection.ConnectionString = $SqlConnString
  35.        
  36.         $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  37.         $SqlCmd.CommandText = $SqlQuery
  38.         $SqlCmd.Connection = $SqlConnection
  39.        
  40.         $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  41.         $SqlAdapter.SelectCommand = $SqlCmd
  42.        
  43.         $DataSet = New-Object System.Data.DataSet
  44.         $a = $SqlAdapter.Fill($DataSet)
  45.        
  46.         $SqlConnection.Close()
  47.        
  48.         $DataSet.Tables | Select-Object -ExpandProperty Rows
  49. }
  50.  
  51. $TablesDBOne = Run-Query -SqlQuery $TableQuery -SqlServer $SqlServerOne -SqlCatalog $FirstDatabase -SqlUser $SqlUsernameOne -SqlPass $SqlPasswordOne | Select-Object -Property TableName
  52.  
  53. $TablesDBTwo = Run-Query -SqlQuery $TableQuery -SqlServer $SqlServerTwo -SqlCatalog $SecondDatabase -SqlUser $SqlUsernameTwo -SqlPass $SqlPasswordTwo | Select-Object -Property TableName
  54.  
  55. Write-Host 'Differences in Tables: '
  56. $Database = @{Name='Database';Expression={if ($_.SideIndicator -eq '<='){'{0} / {1}' -f $FirstDatabase, $SqlServerOne} else {'{0} / {1}' -f $SecondDatabase, $SqlServerTwo}}}
  57. $TableDifference  = Compare-Object $TablesDBOne $TablesDBTwo -SyncWindow (($TablesDBOne.count + $TablesDBTwo.count)/2) -Property TableName | select TableName, $Database
  58.  
  59. if ($log)
  60. {
  61.         $TableDifference | Export-Csv -Path ($file -f $FirstDatabase, $SecondDatabase) -NoTypeInformation
  62. }
  63.  
  64. $TableDifference | Sort-Object -Property TableName, Database
  65.  
  66.  
  67. if ($Column)
  68. {
  69.         #Compare columns in matching tables in DB
  70.         $SameTables = Compare-Object $TablesDBOne $TablesDBTwo -SyncWindow (($TablesDBOne.count + $TablesDBTwo.count)/2) -Property TableName -IncludeEqual -ExcludeDifferent
  71.        
  72.         $ColumnQuery = @"
  73. select sysobjects.name as TableName
  74.         , syscolumns.name as ColumnName
  75.         , systypes.name as Type
  76.         , systypes.Length
  77.         , systypes.XUserType
  78. from sysobjects, syscolumns, systypes
  79. where sysobjects.xtype like 'U' and --specify only user tables
  80.         sysobjects.name not like 'dtproperties' and --specify only user tables
  81.         syscolumns.xusertype= systypes.xusertype --get data type info
  82.         and sysobjects.id=syscolumns.id
  83.         and sysobjects.name = '{0}'
  84. order by sysobjects.name, syscolumns.name, syscolumns.type
  85. "@
  86.        
  87.         Write-Host "`n`n"
  88.         Read-Host 'Press Enter to Check for Column Differences'
  89.        
  90.         foreach ($Table in $SameTables)
  91.         {
  92.                 $ColumnsDBOne = Run-Query -SqlQuery ($ColumnQuery -f $table.tablename)  -SqlServer $SqlServerOne -SqlCatalog $FirstDatabase -SqlUser $SqlUsernameOne -SqlPass $SqlPasswordOne | Select-Object -Property TableName, ColumnName, Type, Length, XUserType
  93.  
  94.                 $ColumnsDBTwo = Run-Query -SqlQuery ($ColumnQuery -f $table.tablename) -SqlServer $SqlServerTwo -SqlCatalog $SecondDatabase -SqlUser $SqlUsernameTwo -SqlPass $SqlPasswordTwo | Select-Object -Property TableName, ColumnName, Type, Length, XUserType
  95.                
  96.                 $ColumnDifference = Compare-Object $ColumnsDBOne $ColumnsDBTwo -SyncWindow (($ColumnsDBOne.count + $ColumnsDBTwo.count)/2) -Property TableName, ColumnName, Type, Length, XUserType | Select-Object TableName, ColumnName, Type, Length, XUserType, $Database
  97.                
  98.                 if ($log -and $ColumnDifference )
  99.                 {
  100.                         $ColumnDifference | Export-Csv -Path ($file -f $Table.TableName,'Columns' ) -NoTypeInformation
  101.                 }
  102.                
  103.                 $ColumnDifference | sort ColumnName, Database
  104.                
  105.         }
  106. }

Submit a correction or amendment below (click here to make a fresh posting)
After submitting an amendment, you'll be able to view the differences between the old and new posts easily.

Syntax highlighting:


Remember me