SQL Server Performance

Script for finding&sync differences between DBs

Discussion in 'Contribute Your SQL Server Scripts' started by Ray D, Jul 16, 2005.

  1. Ray D New Member

    SET NOCOUNT ON<br />GO<br /><br />DECLARE @@src_db nvarchar(32), @@dest_db nvarchar(32)<br />DECLARE @@table_name nvarchar(256), @@primary_key nvarchar(64)<br />DECLARE @@sql_text nvarchar(204<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @@column nvarchar(12<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br /><br />SET @@src_db = 'Decanat6';<br />SET @@dest_db = '[fserver].Decanat6';<br /><br />DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND <br />TABLE_NAME NOT LIKE 'conflict%' and objectproperty(object_id(table_name), 'IsMsSHipped')=0 and<br />table_name in<br />(<br />'MARKINFO',<br />'CONTESTRESULT',<br />'ACADEMICOUTORDER'<br />) <br />OPEN tables<br />FETCH NEXT FROM tables INTO @@table_name<br />WHILE @@FETCH_STATUS = 0<br />BEGIN <br />SELECT @@primary_key = KC.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KC<br />INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON TC.CONSTRAINT_NAME = KC.CONSTRAINT_NAME<br />WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND KC.TABLE_NAME = @@table_name<br /><br />if object_id('tempdb..##different') is not null drop table ##different<br /><br />SET @@sql_text = N' SELECT A.* into ##DIFFERENT FROM ' + @@src_db + N'.dbo.' + @@table_name + N' A WHERE ' + <br />N'EXISTS (SELECT ' + @@primary_key + N' FROM ' + <br />@@dest_db + N'.dbo.' + @@table_name + ' B WHERE A.' + @@primary_key + ' = B.' + <br />@@primary_key + N' AND BINARY_CHECKSUM(*) &lt;&gt; (SELECT BINARY_CHECKSUM(*) FROM ' + <br />@@src_db + N'.dbo.' + @@table_name + N' WHERE ' + @@primary_key + N' = A.' + @@primary_key + N'))'<br />PRINT @@SQL_TEXT<br />EXEC sp_executesql @@sql_text<br />SELECT * FROM ##DIFFERENT<br /> DECLARE columns_cursor CURSOR FOR <br /> SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS<br />WHERE column_name &lt;&gt; @@primary_key AND column_name &lt;&gt; 'rowguid' AND table_name = @@table_name<br /><br />SET @@sql_text = N'UPDATE A SET ';<br /><br /> OPEN columns_cursor<br /> FETCH NEXT FROM columns_cursor INTO @@column<br /> WHILE @@FETCH_STATUS = 0<br /> BEGIN <br />SET @@sql_text = @@sql_text + @@column + N' = B.' + @@column + N',';<br />FETCH NEXT FROM columns_cursor INTO @@column<br />END<br />SET @@sql_text = LEFT(@@sql_text, LEN(@@sql_text) - 1);<br />SET @@sql_text = @@sql_text + ' FROM ' + @@dest_db + N'.dbo.' + @@table_name + N' A,' + <br /> @@src_db + N'.dbo.' + @@table_name + N' B, ##DIFFERENT D WHERE A.' + @@primary_key +<br />N' = B.'+@@primary_key + ' AND D.'+@@primary_key + '=B.'+@@primary_key ;<br /> CLOSE columns_cursor<br /> DEALLOCATE columns_cursor<br /><br />EXEC sp_executesql @@sql_text<br />FETCH NEXT FROM tables INTO @@table_name<br />END<br />CLOSE tables<br />DEALLOCATE tables<br /><br /><br />WBR, Vlad A. Scherbinin

Share This Page