9b6iAeXq

1CREATE PROCEDURE [dbo].[usp_DatabaseValidation]
2    @TableName varchar(50)
3
4AS
5BEGIN
6
7    SET NOCOUNT ON;
8
9    -- parameter = if no table name was passed do them all, otherwise just check the one
10
11    -- create a temp table that lists all tables in target database
12
13    CREATE TABLE #ChkSumTargetTables ([fullname] varchar(250), [name] varchar(50), chksum int);
14    INSERT INTO #ChkSumTargetTables ([fullname], [name], [chksum])
15        SELECT DISTINCT
16            '[MyDatabase].[' + S.name + '].['
17            + T.name + ']' AS [fullname],
18            T.name AS [name],
19            0 AS [chksum]
20        FROM MyDatabase.sys.tables T
21            INNER JOIN MyDatabase.sys.schemas S ON T.schema_id = S.schema_id
22        WHERE 
23            T.name like IsNull(@TableName,'%');
24
25    -- create a temp table that lists all tables in source database
26
27    CREATE TABLE #ChkSumSourceTables ([fullname] varchar(250), [name] varchar(50), chksum int)
28    INSERT INTO #ChkSumSourceTables ([fullname], [name], [chksum])
29        SELECT DISTINCT
30            '[MyLinkedServer].[MyDatabase].[' + S.name + '].['
31            + T.name + ']' AS [fullname],
32            T.name AS [name],
33            0 AS [chksum]
34        FROM [MyLinkedServer].[MyDatabase].sys.tables T
35            INNER JOIN [MyLinkedServer].[MyDatabase].sys.schemas S ON 
36            T.schema_id = S.schema_id
37        WHERE
38            T.name like IsNull(@TableName,'%');;
39
40    -- build a dynamic sql statement to populate temp tables with the checksums of each table
41
42    DECLARE @TargetStmt VARCHAR(MAX)
43    SELECT  @TargetStmt = COALESCE(@TargetStmt + ';', '')
44            + 'UPDATE #ChkSumTargetTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
45            + T.FullName + ') WHERE [name] = ''' + T.Name + ''''
46    FROM    #ChkSumTargetTables T
47
48    SELECT  @TargetStmt
49
50    DECLARE @SourceStmt VARCHAR(MAX)
51    SELECT  @SourceStmt = COALESCE(@SourceStmt + ';', '')
52            + 'UPDATE #ChkSumSourceTables SET [chksum] = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM '
53            + S.FullName + ') WHERE [name] = ''' + S.Name + ''''
54    FROM    #ChkSumSourceTables S
55
56    -- execute dynamic statements - populate temp tables with checksums
57
58    EXEC (@TargetStmt);
59    EXEC (@SourceStmt);
60
61    --compare the two databases to find any checksums that are different
62
63    SELECT  TT.FullName AS [TABLES WHOSE CHECKSUM DOES NOT MATCH]
64    FROM #ChkSumTargetTables TT
65    LEFT JOIN #ChkSumSourceTables ST ON TT.Name = ST.Name
66    WHERE IsNull(ST.chksum,0) <> IsNull(TT.chksum,0)
67
68    --drop the temp tables from the tempdb
69
70    DROP TABLE #ChkSumTargetTables;
71    DROP TABLE #ChkSumSourceTables;
72
73END
74	
75(SELECT 'TableA', * FROM TableA
76EXCEPT
77SELECT 'TableA', * FROM TableB)
78UNION ALL
79(SELECT 'TableB', * FROM TableB
80EXCEPT
81SELECT 'TableB', * FROM TableA)
82	
83SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA
84
85SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB
86	
87SELECT BINARY_CHECKSUM(*) from myTable;
88	
89SELECT
90   ID = IsNull(A.ID, B.ID),
91   AValue = A.Value,
92   BValue = B.Value
93FROM
94   dbo.TableA A
95   FULL JOIN dbo.TableB B
96      ON A.ID = B.ID
97WHERE
98   EXISTS (
99      SELECT A.*
100      EXCEPT SELECT B.*
101   );