CODEWITHSUNDEEP
sqlsql-server
PJ.SQL
PJ.SQL

Reputation: 363

Is there an easy way to compare column names between two tables in SQL Server?

I'm working with two very wide, very similar tables in SQL Server. Anywhere from 5-10 columns exist in one table but not in the other. Is there an easy way to find out which columns exist in one table but not in the other?

Upvotes: 1

Views: 3567

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use information_schema.columns. Here is one method with a full outer join:

select c1.column_name, c2.column_name
from (select c.*
      from information_schema.columns
      where table_name = @table1 and table_schema = @schema1
     ) c1 full outer join
     (select c.*
      from information_schema.columns
      where table_name = @table2 and table_schema = @schema2
     ) c2
     on c1.column_name = c2.column_name
where c1.column_name is null or c2.column_name is null

Upvotes: 3

Related Questions