ramlicious Blogs by Tina & Prabhu

December 23, 2010

Script to compare two SQL databases

Filed under: SQL Server — Prabhuram @ 7:27 am

Here is a simple and a minimal level of SQL scripting for comparing two databases. Download COMPARE_SQL_DATABASES.sql (3.53 kb) and change the script as required. And remember to change the variables that sets the databases to be compared before executing them in SQL Studio. The script starts like this and the complete script is available for download here

———————————————————————–
– REPORT TO GENERATE A COMPARISON LIST BETWEEN TWO SQL DATABASES
– THIS REPORT LIST THE DIFFERING TABLES, VIEWS, COLUMNS, AND ROUTINES
———————————————————————–
–THREE VARIABLES NEEDS TO BE CHANGED BEFORE RUNNING THIS REPORT
–THIS REPORT ASSUMES THAT THE SECOND DATABASE IS ADDED AS A LINKED_SERVER
–@localdb1: FIRST DATABASE
–@linkeddb2: DATABASE TO BE COMPARED AGAINST
–@linkedserver2: LINKED SERVER NAME WHERE @linkeddb2 RESIDES
–
–RUN THIS FROM localdb1
declare @localdb1 varchar(10), @linkeddb2 varchar(10)
declare @linkedserver2 varchar(10)
select @linkedserver2 = 'prod'
select @localdb1 = 'nwind', @linkeddb2 = 'nwind'
print 'Tables in [' + @localdb1 + '] and not in [' + @linkedserver2 + '].[' + @linkeddb2 + ']'
exec ('select TABLE_NAME from [' + @localdb1 + '].information_schema.tables a' +
' where not exists(select * from [' + @linkedserver2 + ']' +
'.[' + @linkeddb2 + '].information_schema.tables b where a.table_name = table_name)'
)
print 'Tables in [' + @linkedserver2 + '].[' + @linkeddb2 + '] and not in [' + @localdb1 + ']'
exec (
'select TABLE_NAME from [' + @linkedserver2 + '].[' + @linkeddb2 + '].information_schema.tables a' +
' where not exists(select * from [' + @localdb1 + ']' +
'.information_schema.tables b where a.table_name = table_name)'
)

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress