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