CODEWITHSUNDEEP
sqlsql-server
James Winans
James Winans

Reputation: 87

Finding Unique Table/Column Combinations Across SQL Databases

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?

Upvotes: 2

Views: 1032

Answers (2)

Salman Kasbati
Salman Kasbati

Reputation: 342

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)

Upvotes: 1

Tom H
Tom H

Reputation: 47464

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

Upvotes: 1

Related Questions