PoshCode Logo PowerShell Code Repository

Compare SQL Tables II by Bernd Kriszio 31 months ago
embed code: <script type="text/javascript" src="http://PoshCode.org/embed/2390"></script>download | new post

Another version to compare 2 SQL Tables. Uses code to exclude specified columns from SELECT *

  1. function Convert-TableToList
  2. {
  3.     param(
  4.         $t,
  5.         $colid = 0
  6.     )
  7.     $t | % {$_.item($colid)}
  8. }
  9.  
  10.  
  11. function Compare-Tables
  12. {
  13.     param(
  14.         $name,
  15.         $db1,
  16.         $db2,
  17.         $exclude = @()
  18.         )
  19.  
  20. # @bernd_k http://pauerschell.blogspot.com/
  21. # requires on sqlise http://sqlpsx.codeplex.com/
  22.  
  23. $sql = "select name from sys.columns  where object_id = object_id('$db1..$name') order by column_id"
  24. Invoke-ExecuteSql  $sql 'variable' columns
  25.  
  26. $columns = Convert-TableToList $columns | % { if ($exclude -notcontains $_) {$_} }
  27. $columnlist = $columns -join ', '
  28. $sql = @"
  29. Select 1 [table], $columnlist from $db1..$name
  30. except
  31. Select 1 [table], $columnlist from $db2..$name
  32. union
  33. Select 2 [table], $columnlist from $db2..$name
  34. except
  35. Select 2 [table], $columnlist from $db1..$name
  36. ORDER by 2
  37. "@
  38. $sql
  39. Invoke-ExecuteSql  $sql 'grid'
  40. }
  41.  
  42. # Compare-Table2  sometable db1 db2 -ex @('colx', 'coly')

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