Finding Unique Table/Column Combinations Across SQL Databases

Question

I have 4 databases with similar schema's, and I'm trying to create a query to return just the table, column pairs that exist ONLY in database 1 and do not exist in database 2, 3, or 4.

Currently I can return the symmetric difference between database 1 and 2 via the following query...

select table_name, column_name from (
     select table_name, column_name from [Database1].information_schema.columns
     union all
     select table_name, column_name from [Database2].information_schema.columns) as tmp
     group by table_name, column_name having count(*) = 1

However, in trying to isolate just those columns in database 1, and doing the same across all 4 databases, things are getting complicated. What is the cleanest solution for this query?

Answer 1

SELECT
     D1.table_name,
     D1.column_name
FROM
     Database1.information_schema.columns D1
LEFT OUTER JOIN Database2.information_schema.columns D2 ON
     D2.table_name = D1.table_name AND
     D2.column_name = D1.column_name
LEFT OUTER JOIN Database3.information_schema.columns D3 ON
     D3.table_name = D1.table_name AND
     D3.column_name = D1.column_name
LEFT OUTER JOIN Database4.information_schema.columns D4 ON
     D4.table_name = D1.table_name AND
     D4.column_name = D1.column_name
WHERE
     D2.table_name IS NULL AND
     D3.table_name IS NULL AND
     D4.table_name IS NULL

Answer 2

I can come up with this at first shot, which can be a head start. This hasn't been tested though, just another alternative maybe:

select table_name, column_name from [Database1].information_schema.columns d1 where table_name || '+' || column_name not in (select table_name || '+' || column_name from [Database2].information_schema.columns)

and table_name || '+' || column_name not in (select table_name || '+' || column_name from [Database3].information_schema.columns)

and table_name || '+' || column_name not in (select table_name || '+' || column_name from [Database4].information_schema.columns)