Compare tables and columns in two schemas in SQL Server schemas

Query below compares columns (names) in tables between two SQL Server schemas. It shows columns missing in either of two schemas.

Query

select isnull(schema1.table_name, schema2.table_name) as [table],
       isnull(schema1.column_name, schema2.column_name) as [column],
       schema1.column_name as database1,
       schema2.column_name as database2
from
(select tab.name as table_name, 
        col.name as column_name
 from sys.tables as tab
 join sys.columns as col
      on tab.object_id = col.object_id
 where schema_name(tab.schema_id) = 'schema_1') schema1
full join
(select tab.name as table_name, 
        col.name as column_name
 from sys.tables as tab
 join sys.columns as col
      on tab.object_id = col.object_id
 where schema_name(tab.schema_id) = 'schema_2') schema2
on schema1.table_name = schema2.table_name
and schema1.column_name = schema2.column_name
where (schema1.column_name is null or schema2.column_name is null)
order by 1, 2, 3

Instruction

Replace schema_1 and schema_2 with names of two of your schemas (in SQL Server database) that you'd like to compare.

Columns

  • table - name of the table
  • column - name of column
  • schema1 - if column exists in a table in schema 1 then column contains its name (repeats it from column column)
  • schema2 - if column exists in a table in schema 2 then column contains its name (repeats it from column column)

Rows

  • One row represents one distinct name of column in specific table.
  • Scope of rows: all distinct columns in that exist only in one of the compared databases.
  • Ordered by schema, table and column name

Sample results

0
There are no comments. Click here to write the first comment.