How to get Difference between two databases?

 

As a DBA one of the task one would face at some point of time is to get the differenced between two databases. Generally we may resort to some third party tools such as SQL Diff, SSDT Schema compare etc.  Being a DBA it should not be that hard to get the difference between two databases. We could use the SQL Server's Object Catalog views and get all the differences we want.

 

Here are some general differences we want to compare

1. Tables present in Database 1 and Not present in Database 2
2. Tables not present in Database 1 and Present in Database 2
3. Tables present in Both the database But have column level changes
• Data type Changes
• Newly added columns
• Columns which were dropped

You can use the following set to queries to get the differences.

1. Tables present in Database 1 and Not present in Database 2

                    SELECT  t.name as "TableName FROM DB1.sys.tables t

            EXCEPT

            SELECT  t.name  FROM DB2.sys.tables t

 
2. Tables not present in Database 1 and Present in Database 2


                    SELECT  t.name as "TableName FROM DB2.sys.tables t

            EXCEPT

            SELECT  t.name  FROM DB1.sys.tables t

3. Column Changes(Data Type Changes)



                   SELECT  T.[name] AS [table_name], AC.[name] AS [column_name], 
              TY.[name] AS system_data_type,Ac.max_Length, Ac.precision,Ac.scale,
              T1.[name] AS [table_name_Old], AC1.[name] AS [column_name_old], 
              TY1.[name] AS system_data_type_Old
           FROM    DB1.sys.[tables] AS T 
           INNER JOIN DB1.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
           INNER JOIN DB1.sys.[types] TY ON AC.[user_type_id] = TY.[user_type_id]
           INNER JOIN DB2.sys.[tables] AS T1  on T.name=T1.name
           INNER JOIN DB2.sys.[all_columns] AC1 ON T1.[object_id] =
           AC1.[object_id] AND AC.name=AC1.name INNER JOIN DB2.sys.[types] TY1 ON
           AC1.[user_type_id] =TY1.[user_type_id] AND TY.name<>TY1.name
           ORDER BY t.name

4. Column Changes (Newly added Columns):
           SELECT Table_Name, Column_name,  type_name(b.user_type_id), b.max_length,
        B.precision,B.scale
        FROM
        (
            SELECT  T.[name] AS [table_name], AC.[name] AS [column_name] 
            FROM    DB1.sys.[tables] AS T 
            INNER JOIN DB2.MASDirect.sys.[tables] tx ON t.name=tx.name
            INNER JOIN [MASNEW].sys.[all_columns] AC ON T.[object_id] =
              AC.[object_id] 
            INNER JOIN [MASNEW].sys.[types] TY ON AC.[system_type_id] =
             TY.[system_type_id]
            EXCEPT
             SELECT 
             T1.[name] AS [table_name], AC1.[name] AS [column_name]   
             FROM   DB2.sys.[tables] AS T1  
             INNER JOIN DB2.sys.[all_columns] AC1 ON T1.[object_id] =
             AC1.[object_id]
            INNER JOIN DB2.sys.[types] TY1 ON AC1.[system_type_id] =
            TY1.[system_type_id]
            ) as A
            INNER JOIN DB1.sys.all_columns AS b ON a.table_name=

            OBJECT_NAME(object_id,db_id('DB1')) AND a.column_name=b.name
          ORDER BY table_name






5. Column Changes(Columns which were dropped)
         SELECT 
          T1.[name] AS [table_name], AC1.[name] AS [column_name]   
          FROM    DB2.sys.[tables] AS T1  
          INNER JOIN DB1.sys.[tables] tx on t1.name=tx.name
          INNER JOIN DB2.sys.[all_columns] AC1 ON T1.[object_id] =
          AC1.[object_id]
         INNER JOIN DB2.sys.[types] TY1 ON AC1.[system_type_id] =
         TY1.[system_type_id]
       EXCEPT
        SELECT 
          T.[name] AS [table_name], AC.[name] AS [column_name] 
         FROM DB1.sys.[tables] AS T  
        INNER JOIN DB2.sys.[tables] tx on t.name=tx.name
        INNER JOIN DB1.sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
       INNER JOIN DB1. sys.[types] TY ON AC.[system_type_id] =
        TY.[system_type_id]


These queries may come handy. Hope it is useful to someone.