CODEWITHSUNDEEP
sqlsql-server
ASH
ASH

Reputation: 20342

How can I select matching column names between multiple tables?

I have close to 100 tables and a lot have very similar names, beginning with names like 'STANDARD' and 'MARKIT', and so on and so forth. I'd like to find all column names that are the same for these tables. So, I would like to search all tables that start with 'STANDARD' have the same field names, like 'Field1', 'Field2', 'Field3', and 'Field4'. I'm guessing it would be some combination of sys.columns.name and sys.tables, but I don't know for sure. I think the SQL below is a good start, but I won't know what the common columns are before I run the script.

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
WHERE
  tables.name like '%STANDARD%' AND ... something else ...

Upvotes: 0

Views: 1891

Answers (2)

Lajos Arpad
Lajos Arpad

Reputation: 77002

You were on the right track, you need to join sys.tables with sys.columns and here I'm using a trick to attempt to find the columns and group by table name, keeping only the groups where all the fields were found (untested)

SELECT      t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name LIKE '%STANDARD%' and c.name in ('Field1', 'Field2', 'Field3', 'Field4')
GROUP BY t.name
HAVING count(*) = 5
ORDER BY t.name

EDIT

The following query finds column names that are shared accross multiple tables

select c.ColumnName, count(*)
from sys.columns c
join sys.tables t
on c.object_id = t.object_id
group by c.ColumnName
having count(*) > 2;

You can use the idea above as a subquery if needed.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270873

If I understand correctly, you want the "standard" tables that have all specified columns.

You can use INFORMATION_SCHEMA.COLUMNS.

select table_schema, table_name
from information_schema.columns c
where table_name like 'STANDARD%' and
      table_schema = @schema and
      column_name in ('field1', 'field2', 'field3', 'field4')
group by table_schema, table_name
having count(*) = 4;  -- has all four columns

Upvotes: 1

Related Questions