Monday, December 30, 2013

Queries to figure out which columns are in multiple tables

I have two tables, TableA and TableB, and I want to know which columns belong in each table.

Here's two queries that get me that info

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME IN ('TableA', 'TableB')
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1

--To add more tables, add the table name in the IN() clause, and increment the COUNT(*) > #

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableA'
INTERSECT
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TableB'

--To add more tables, add another INTERSECT + SELECT


No comments:

Post a Comment

There was an error in this gadget