Compare and auto fix conflicts between two databases T-sql

Question

Consider we have a database for an windows application it's for 2 years ago, now we have changed many things in database so there is differences and we don't know what are the differences.

I don't want to use compare tools like Red Gate compare tool.

What's the best way to see new columns are in new database :

Query result like :

New Db Name | Schema | Table Name | Column Name | Default Value | IsNullable | DataType
NewDb       | dbo    | myTable    | newColumn   | ''            | No         | nvarchar(30)

Thank you

---

Edit : I need a query after this to fix the conflicts so both databases be the same :

Add NewDb columns to OldDb,delete OldDb columns that are not in NewDb and update all columns to NewDb version ( if two columns datatype or something is different in two databases. set the NewDb's to OldDb too.)

Answer 1

Columns in old database but not in new , or default value, nullable and data type changed:

SELECT t1.*,
       t2.*
FROM   [OldDb].[INFORMATION_SCHEMA].[COLUMNS] t1
       LEFT JOIN [NewDb].[INFORMATION_SCHEMA].[COLUMNS] t2
         ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE  t2.COLUMN_NAME IS NULL
        OR ( t1.TABLE_NAME = t2.TABLE_NAME
             AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
             AND t1.COLUMN_NAME = t2.COLUMN_NAME
             AND ( t1.DATA_TYPE != t2.DATA_TYPE
                    OR t1.IS_NULLABLE != t2.IS_NULLABLE
                    OR t1.CHARACTER_MAXIMUM_LENGTH != t2.CHARACTER_MAXIMUM_LENGTH
                    OR t1.COLUMN_DEFAULT != t2.COLUMN_DEFAULT ) )
ORDER  BY t1.COLUMN_NAME 

Columns in new database but not in old :

You can change [OldDb] place with [NewDb] in query

I wrote this question and answer because I've needed this and I've spent so much of time on this.

So I hope everyone can find and use this easily :). I'm looking forward for opinions for make this better.

Answer 2

While nothing wrong with your own approach, I might prefer EXCEPT to grab all the differences here...

;WITH 
 a AS (
    SELECT *
    FROM [OldDB].INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
      AND TABLE_NAME = 'foo_table'
 )
,b AS (
    SELECT *
    FROM [NewDB].INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'dbo'
      AND TABLE_NAME = 'foo_table'
 )
,inanotb AS (
    SELECT * FROM a
    EXCEPT 
    SELECT * FROM b
)
,inbnota AS (
    SELECT * FROM b
    EXCEPT 
    SELECT * FROM a
)
SELECT 'In A not B', *
FROM inanotb
UNION ALL 
SELECT 'In B not A', *
FROM inbnota;

Answer 3

Change the oldDb and newDb parameters and execute the query. This will tell you also which columns were modified, which are new and which does not exist in the old database.

DECLARE @oldDb as nvarchar(max) = 'sourceDb';
DECLARE @newDb as nvarchar(max) = 'targetDb';
DECLARE @sql as nvarchar(max);

SET @sql = 'SELECT CASE WHEN t2.COLUMN_NAME IS NULL THEN ''New'' ELSE ''Modified'' END as ''Status'',
        t1.TABLE_CATALOG+''.''+t1.TABLE_SCHEMA+''.''+t1.TABLE_NAME+''.''+t1.COLUMN_NAME as ''OldColumn'',
        t1.COLUMN_DEFAULT,t2.COLUMN_DEFAULT as ''NewDefault'', 
        t1.IS_NULLABLE,t2.IS_NULLABLE as ''NewNullable'',
        t1.DATA_TYPE,t2.DATA_TYPE as ''NewType'', 
        t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH as ''NewLength''
FROM   ['+@oldDb+'].[INFORMATION_SCHEMA].[COLUMNS] t1
       LEFT JOIN ['+@newDb+'].[INFORMATION_SCHEMA].[COLUMNS] t2
         ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE  t2.COLUMN_NAME IS NULL
        OR ( t1.TABLE_NAME = t2.TABLE_NAME
             AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
             AND t1.COLUMN_NAME = t2.COLUMN_NAME
             AND ( t1.DATA_TYPE != t2.DATA_TYPE
                    OR t1.IS_NULLABLE != t2.IS_NULLABLE
                    OR t1.CHARACTER_MAXIMUM_LENGTH != t2.CHARACTER_MAXIMUM_LENGTH
                    OR t1.COLUMN_DEFAULT != t2.COLUMN_DEFAULT ) )
UNION
    SELECT ''NotInSource'' as ''Status'',
        t1.TABLE_CATALOG+''.''+t1.TABLE_SCHEMA+''.''+t1.TABLE_NAME+''.''+t1.COLUMN_NAME as ''OldColumn'',
        t1.COLUMN_DEFAULT,t2.COLUMN_DEFAULT as ''NewDefault'', 
        t1.IS_NULLABLE,t2.IS_NULLABLE as ''NewNullable'',
        t1.DATA_TYPE,t2.DATA_TYPE as ''NewType'', 
        t1.CHARACTER_MAXIMUM_LENGTH,t2.CHARACTER_MAXIMUM_LENGTH as ''NewLength''
FROM   ['+@newDb+'].[INFORMATION_SCHEMA].[COLUMNS] t1
       LEFT JOIN ['+@oldDb+'].[INFORMATION_SCHEMA].[COLUMNS] t2
         ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE  t2.COLUMN_NAME IS NULL'

EXEC sp_executesql @sql;

Also you can compare the modified procedures from a database to another with the following request :

DECLARE @oldDb as nvarchar(max) = 'sourceDb';
DECLARE @newDb as nvarchar(max) = 'targetDb';
DECLARE @sql as nvarchar(max);

SET @sql = 'SELECT 
CASE WHEN proc2.modify_date > proc1.modify_date 
THEN ''Modified-OnTarget'' 
ELSE CASE WHEN proc2.modify_date < proc1.modify_date 
THEN ''Modified-OnSource'' 
ELSE ''New'' END END as ''Status'' 
, proc1.name 
, CASE WHEN proc2.modify_date > proc1.modify_date 
THEN proc2.modify_date 
ELSE proc1.modify_date END as ''Modified'' 
, CASE WHEN proc2.modify_date > proc1.modify_date 
THEN rout2.[ROUTINE_DEFINITION] 
ELSE rout1.[ROUTINE_DEFINITION] END as ''Definition'' 
FROM ['+@oldDb+'].sys.procedures proc1 
LEFT JOIN ['+@newDb+'].sys.procedures proc2 
ON proc1.name = proc2.name 
LEFT JOIN ['+@oldDb+'].[INFORMATION_SCHEMA].[ROUTINES] rout1 
ON proc1.name = rout1.[SPECIFIC_NAME] 
AND rout1.[ROUTINE_TYPE] = ''PROCEDURE'' 
LEFT JOIN ['+@newDb+'].[INFORMATION_SCHEMA].[ROUTINES] rout2 
ON proc2.name = rout2.[SPECIFIC_NAME] 
AND rout2.[ROUTINE_TYPE] = ''PROCEDURE'' 
AND proc2.type_desc = ''SQL_STORED_PROCEDURE'' 
WHERE proc1.type_desc = ''SQL_STORED_PROCEDURE'' 
AND (proc2.object_id IS NULL 
OR rout1.[ROUTINE_DEFINITION] != rout2.[ROUTINE_DEFINITION]) 
ORDER BY 1 '

EXEC sp_executesql @sql;