PoshCode Logo PowerShell Code Repository

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

This script compares the column structure between two databases with tables of the same name. Table names to compare can be passed in as an array or piped in either as string values, or as a property of Name or TableName.

  1. param(  [String[]]$Table = $null,
  2.                 $SqlServerOne = 'YourDatabaseServer',
  3.         $FirstDatabase = 'FirstDatabaseToCompare',
  4.         $SqlUsernameOne = 'SQL Login',
  5.         $SqlPasswordOne = 'SQL Password',
  6.         $SqlServerTwo = 'YourDatabaseServer',
  7.         $SecondDatabase = 'SecondDatabaseToCompare',
  8.         $SqlUsernameTwo = 'SQL Login',
  9.         $SqlPasswordTwo = 'SQL Password',
  10.         $FilePrefix = 'Log',
  11.         [switch]$Log,
  12.         [switch]$Column)
  13.                
  14. if ($Input)
  15. {
  16.         foreach ($item in $Input)
  17.         {
  18.                 if ($item -is [string])
  19.                 {
  20.                         $Table += $item
  21.                 }
  22.                 else
  23.                 {
  24.                         foreach ($property in $item.psobject.properties)
  25.                         {
  26.                                 if ('name', 'TableName' -contains $property.name)
  27.                                 {
  28.                                         $Table += $Property.value
  29.                                         break
  30.                                 }
  31.                         }
  32.                 }
  33.         }
  34. }
  35.  
  36. if ($Table.count -eq 0)
  37. {
  38.         throw 'A table to compare is required.'
  39. }
  40.  
  41. $File = $FilePrefix + '{0}-{1}.csv'
  42.  
  43. $OFS = "', '"
  44.  
  45. $ColumnQuery = @"
  46. SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName, systypes.name AS type,
  47.         syscolumns.length
  48. FROM systypes
  49.  INNER JOIN syscolumns ON systypes.xusertype = syscolumns.xusertype  --get data type info
  50.  INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
  51. WHERE    
  52. sysobjects.name IN ('$Table')
  53. "@
  54.  
  55. function Run-Query()
  56. {
  57.         param (
  58.         $SqlQuery,
  59.         $SqlServer,
  60.         $SqlCatalog,
  61.         $SqlUser,
  62.         $SqlPass
  63.         )
  64.        
  65.         $SqlConnString = "Server = $SqlServer; Database = $SqlCatalog; user = $SqlUser; password = $SqlPass"
  66.         $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
  67.         $SqlConnection.ConnectionString = $SqlConnString
  68.        
  69.         $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
  70.         $SqlCmd.CommandText = $SqlQuery
  71.         $SqlCmd.Connection = $SqlConnection
  72.        
  73.         $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
  74.         $SqlAdapter.SelectCommand = $SqlCmd
  75.        
  76.         $DataSet = New-Object System.Data.DataSet
  77.         $a = $SqlAdapter.Fill($DataSet)
  78.        
  79.         $SqlConnection.Close()
  80.        
  81.         $DataSet.Tables | Select-Object -ExpandProperty Rows
  82. }
  83.  
  84. [String[]]$Properties = 'TableName', 'ColumnName', 'Type', 'Length'
  85.  
  86. Write-Debug "Checking Tables: '$Table'"
  87.  
  88. $ColumnsDBOne = Run-Query -SqlQuery $ColumnQuery -SqlServer $SqlServerOne -SqlCatalog $FirstDatabase -SqlUser $SqlUsernameOne -SqlPass $SqlPasswordOne | Select-Object -Property $Properties
  89.  
  90. $ColumnsDBTwo = Run-Query -SqlQuery $ColumnQuery -SqlServer $SqlServerTwo -SqlCatalog $SecondDatabase -SqlUser $SqlUsernameTwo -SqlPass $SqlPasswordTwo | Select-Object -Property $Properties
  91.  
  92. Write-Host 'Differences in Columns: '
  93. $Database = @{Name='Database';Expression={if ($_.SideIndicator -eq '<='){'{0} / {1}' -f $FirstDatabase, $SqlServerOne} else {'{0} / {1}' -f $SecondDatabase, $SqlServerTwo}}}
  94.  
  95. $ColumnDifference = Compare-Object $ColumnsDBOne $ColumnsDBTwo -SyncWindow (($TablesDBOne.count + $TablesDBTwo.count)/2) -Property $Properties | select 'TableName', 'ColumnName', 'Type', 'Length', $Database
  96.  
  97. if ($log)
  98. {
  99.         $ColumnDifference | Export-Csv -Path ($file -f $FirstDatabase, $SecondDatabase) -NoTypeInformation
  100. }
  101.  
  102. $OFS = ', '
  103. $ColumnDifference | Sort-Object -Property 'TableName', 'ColumnName', 'Type', 'Length', 'Database'

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