4

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.)

4

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.

  • Hi, just curious, why did you choose a full outer join over a left join? – user122754 Jun 9 '17 at 11:49
  • 1
    Have just done some testing on our existing tables and left join performs considerably better with the same output. FAR less logical reads as well as run time (102906704 reads with full outer and only 129767 with left). – user122754 Jun 9 '17 at 11:55
  • You are right. I tried that too, it's better, I'll edit this. Thank you – Mohamad Shahrestani Jun 9 '17 at 11:56
  • Very usefull for comparing what's done in Dev/PreProd and if any work has been done without going through Dev/PreProd status – Hybris95 Jun 9 '17 at 11:58
  • 1
    Also: you one more query to show the columns that have changed (type, nullability, default value, etc) – ypercubeᵀᴹ Jun 9 '17 at 12:08
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;
  • This is not working. Am I missing something ? – Mohamad Shahrestani Jun 9 '17 at 17:34
  • @MohamadShahrestani - Might need something more that 'not working' to go on... i.e. works for me – jimbobmcgee Jun 9 '17 at 18:11
  • That does not show the differences. – Mohamad Shahrestani Jun 9 '17 at 18:25
  • It shows all the columns for table dbo.foo_table in database [OldDB] that are not in dbo.foo_table in database [NewDB] and vice-versa. If you are seeing zero rows, are you sure there actually are differences...? – jimbobmcgee Jun 9 '17 at 18:44
1

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;
  • Only problem is your answer is not easy to edit, but very nice <3 I hope you improve it. – Mohamad Shahrestani Jun 16 '17 at 14:08
  • Maintenability is not great, but the fact parameters are easely modifiable make it a little more generic, also it adds the reverse side check (columns not present in the source db) – Hybris95 Jun 16 '17 at 14:10
  • It's more cool now mate thanks, I'll take accept from my answer to yours. – Mohamad Shahrestani Jul 9 '17 at 10:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.