Sql Server | Compare 2 tables columns

Here is a sql statement for comapring two tables (may be in diffrent DBs) columns.
This SQL statement compare only if column exist in both or not and return the difference.
You may improve the sql statement for comapring more... :)
I hope this sql script will be helpful...

DECLARE @Db1 NVARCHAR(MAX)
DECLARE @Table1 NVARCHAR(MAX)
DECLARE @Db2 NVARCHAR(MAX)
DECLARE @Table2 NVARCHAR(MAX)
DECLARE @Sql NVARCHAR(MAX)

SET @Db1 = 'MMIS_SNAP'
SET @Table1 = 'CodeTablesData'
SET @Db2 = 'MMIS_SNAP'
SET @Table2 = 'CodeTables'
SET @Sql = ' ' +
' SELECT ''in ' + @Db1 + '.' + @Table1 + ' --- not in ' + @Db2 + '.' + @Table2 + ''' AS TITLE, a.TABLE_CATALOG, a.column_name ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE a.column_name NOT IN (SELECT column_name ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table2 + ''')) ' +
' AND a.table_name IN (''' + @Table1 + ''') ' +

' UNION ALL ' +

' SELECT ''in ' + @Db2 + '.' + @Table2 + ' --- not in ' + @Db1 + '.' + @Table1 + ''' AS TITLE, a.TABLE_CATALOG, a.column_name ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE a.column_name NOT IN (SELECT column_name ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table1 + ''')) ' +
' AND a.table_name IN (''' + @Table2 + ''') ' +
''

EXEC (@Sql)

You may create store-procedure from this script, and the automaticly run it
on all your tables.
This may be automatic table columns comparison...


post your additional information as comments, thanks


ANOTHER SAMPLE, faster compare with 'Allow null' & 'Data type':

SET @Db1 = 'TestDB'
SET @Table1 = 'Users'
SET @Db2 = 'TestDB'
SET @Table2 = 'Users2'

SET @Sql = ' ' +
' SELECT ''in ' + @Db1 + '.' + @Table1 + ' --- not in ' + @Db2 + '.' + @Table2 + ''' AS TITLE, ' +
' a.TABLE_CATALOG, ' +
' a.COLUMN_NAME, ' +
' a.IS_NULLABLE, ' +
' a.DATA_TYPE ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE NOT EXISTS ( ' +
' SELECT b.COLUMN_NAME ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table2 + ''') ' +
' AND b.COLUMN_NAME = a.COLUMN_NAME ' +
' AND b.IS_NULLABLE = a.IS_NULLABLE ' +
' AND b.DATA_TYPE = a.DATA_TYPE ' +
' ) ' +
' AND a.table_name IN (''' + @Table1 + ''') ' +
' ' +
' UNION ALL ' +
' ' +
' SELECT ''in ' + @Db2 + '.' + @Table2 + ' --- not in ' + @Db1 + '.' + @Table1 + ''' AS TITLE, ' +
' a.TABLE_CATALOG, ' +
' a.COLUMN_NAME, ' +
' a.IS_NULLABLE, ' +
' a.DATA_TYPE ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE NOT EXISTS ( ' +
' SELECT b.COLUMN_NAME ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table1 + ''') ' +
' AND b.COLUMN_NAME = a.COLUMN_NAME ' +
' AND b.IS_NULLABLE = a.IS_NULLABLE ' +
' AND b.DATA_TYPE = a.DATA_TYPE ' +
' ) ' +
' AND a.table_name IN (''' + @Table2 + ''') ' +
' '

PRINT @Sql
EXEC (@Sql)