PoshCode Archive  Artifact [28a04dee6b]

Artifact 28a04dee6b1ca462ed8371d9e1a60aca6e04183b61c5d87898b75a24742406a2:

• File Compare-SQL-Tables-II.ps1 — part of check-in [ca48ffbb26] at 2018-06-10 13:09:29 on branch trunk — Another version to compare 2 SQL Tables. Uses code to exclude specified columns from SELECT * (user: Bernd Kriszio size: 1341)

# encoding: ascii
# api: powershell
# title: Compare SQL Tables II
# description: Another version to compare 2 SQL Tables. Uses code to exclude specified columns from SELECT *
# version: 0.1
# type: function
# author: Bernd Kriszio
# license: CC0
# function: Convert-TableToList
# x-poshcode-id: 2390
# x-archived: 2014-08-18T21:32:35
# x-published: 2011-11-26T02:32:00
#
#
function Convert-TableToList
{
    param(
        $t,
        $colid = 0
    )
    $t | % {$_.item($colid)}
}


function Compare-Tables
{
    param(
        $name,
        $db1,
        $db2,
        $exclude = @()
        )

# @bernd_k http://pauerschell.blogspot.com/
# requires on sqlise http://sqlpsx.codeplex.com/

$sql = "select name from sys.columns  where object_id = object_id('$db1..$name') order by column_id"
Invoke-ExecuteSql  $sql 'variable' columns

$columns = Convert-TableToList $columns | % { if ($exclude -notcontains $_) {$_} }
$columnlist = $columns -join ', '
$sql = @"
Select 1 [table], $columnlist from $db1..$name
except
Select 1 [table], $columnlist from $db2..$name
union
Select 2 [table], $columnlist from $db2..$name
except
Select 2 [table], $columnlist from $db1..$name
ORDER by 2
"@
$sql
Invoke-ExecuteSql  $sql 'grid'
}

# Compare-Table2  sometable db1 db2 -ex @('colx', 'coly')