This article is half-done without your Comment! *** Please share your thoughts via Comment ***
When we are working with different versions of database, database professionals are comparing the objects between databases of SQL Server.
They must make sure about that all databases are in sync.
Using this T-SQL Script, you can compare two databases and find a list of unmatched objects like: Constraints, Tables, Views, Stored Procedure, Triggers.
Sample demonstration:
First, Create two sample databases:
|
1 2 3 4 |
CREATE DATABASE ABC GO CREATE DATABASE XYZ GO |
Create few sample tables in both the databases:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE TABLE ABC.dbo.tbl_Test ( ID INT ,MyDate DATE ) GO CREATE TABLE ABC.dbo.tbl_MyTest ( ID INT ,Name VARCHAR(50) ) GO CREATE TABLE XYZ.dbo.tbl_Test ( ID INT ,MyDate DATE ) GO CREATE TABLE XYZ.dbo.tbl_MyFinal ( ID INT ,Name VARCHAR(50) ) GO |
T-SQL Script to compare objects between two databases:
According to your databases, You can change the name of @SourceDatabase and @DestinationDatabase.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
DECLARE @SourceDatabase VARCHAR(50) DECLARE @DestinationDatabase VARCHAR(50) DECLARE @SQL VARCHAR(MAX) SELECT @SourceDatabase = 'ABC' SELECT @DestinationDatabase = 'XYZ' SELECT @SQL = ' SELECT ISNULL(S.name,D.name) ObjectName ,CASE WHEN S.object_id IS NULL THEN D.type_desc + '' is missing in the Source Database: ' + @SourceDatabase + ''' WHEN D.object_id IS NULL THEN S.type_desc + '' is missing in the Destination Database: ' + @DestinationDatabase + ''' END ''Status'' FROM ( SELECT * FROM ' + @SourceDatabase + '.SYS.objects WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'') ) AS S FULL OUTER JOIN ( SELECT * FROM ' + @DestinationDatabase + '.SYS.objects WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'') ) AS D ON S.name = D.name AND S.type = D.type ORDER BY isnull(S.type,D.type) ' EXEC (@Sql) |
The Result:
|
1 2 3 4 5 |
ObjectName Status ------------------ ------------------------------------------------------- tbl_Test NULL tbl_MyFinal USER_TABLE is missing in the Source Database: ABC tbl_MyTest USER_TABLE is missing in the Destination Database: XYZ |
