How do I compare two columns from different tables in different databases?-CodePudding

Say I have Table_1 in Database_1 with 25 Columns and say I have Table_2 in Database_2 with 19 Columns I want to compare the columns in Table_1 and Table_2 and output Columns that exist in Table_1 but not in Table_2

I tried

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='Table_1'
EXCEPT
SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME='Table_2'

The problem is: If I am in Database_1 it only finds variables in Table_1 and return empty list for Table_2, if I am in Database_2 it only finds variables in Table_2 and returns empty list for Table_1. If say I am in Master, it returns empty list for both Table_1 and Table_2. How do I properly locate each table and their variables from one database?

CodePudding user response：

You can access any database object from any database context by fully qualifying the object name in the form of database.schema.object.

Using SQL Server you are better off using the sys schema, which (if performance matters) is better than using the information_schema schema.

So you can do

select name
from database_1.sys.columns
where object_id=object_id(N'database_1.sys.table_1')
except
select name
from database_2.sys.columns
where object_id=object_id(N'database_2.sys.table_2')