Is there a way to search all SQL tables by column name?

Question

In this answer, you can search all tables for a column by column name.

Say I have a list of columns like this:

DECLARE @columnNames TABLE (Id varchar(30))

INSERT INTO @columnNames 
VALUES ('xColumn1Name'), ('xColumn2Name'), ('xColumn3Name')

I want to find all tables that have at least these three columns. Is it possible to do a foreach loop with the code below, or is there a simpler way?

SELECT      
    COLUMN_NAME AS 'ColumnName',  -- this code will get all tables with a column by name @xColumnName, but I would like to pass in a list
    TABLE_NAME AS 'TableName'
FROM        
    INFORMATION_SCHEMA.COLUMNS
WHERE       
    COLUMN_NAME LIKE '@xColumnName'
ORDER BY    
    TableName, ColumnName;

The table must have all 3 colums named in the list, and it would be cool if I could filter out tables that do not have a certain column or list of columns

To clarify, are you wanting tables that have all three or any of these three? — Kupokev, Jan 07 '21 at 15:14

score 1 · Answer 1 · answered Jan 07 '21 at 15:22

This should get your initial goal.

SELECT 
    [TableName]
FROM (
    SELECT      
        COLUMN_NAME AS 'ColumnName',  -- this code will get all tables with a column by name @xColumnName, but I would like to pass in a list
        TABLE_NAME AS 'TableName',
        ROW_NUMBER() OVER(PARTITION BY TABLE_NAME ORDER BY COLUMN_NAME) rn
    FROM        
        INFORMATION_SCHEMA.COLUMNS
    WHERE       
        COLUMN_NAME IN ('xColumn1Name', 'xColumn2Name', 'xColumn3Name')
) a
WHERE rn >= 3

For a short explanation, this query will look through the information schema to find any of these columns in a table. The ROW_NUMBER() then basically groups the columns by table. If there are 3 or more results (rn) then all 3 columns are there.

Since it is a sub select, you can also filter the outside select for particular columns if you want.

And how would you parameterize this for unknow numbers of columns? — Charlieface, Jan 07 '21 at 15:22
Depends on where that list comes from. Easiest way would to replace COLUMN_NAME IN ('xColumn1Name', 'xColumn2Name', 'xColumn3Name') with a COLUMN_NAME IN SELECT columns FROM tableofcolumnnames — Kupokev, Jan 07 '21 at 15:24

score 1 · Accepted Answer · edited Mar 16 '21 at 12:33

This is a relational division question. There are a few methods to solve this as Joe Celko writes. The common solution is as follows:

DECLARE @columnNames TABLE (Id varchar(30))

INSERT INTO @columnNames 
VALUES ('xColumn1Name'), ('xColumn2Name'), ('xColumn3Name')

select t.name
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join @columnNames cn on cn.Id = c.name
group by t.object_id, t.name
having count(*) >=
    (select count(*) from @columnNames);

What this says is: give me all tables, where the number of columns which match the list @columnName is the same or more as the number in that list, in other words tehre is a match for every column.

score 0 · Answer 3 · answered Jan 07 '21 at 15:26

You could use INTERSECT to combine different result sets. This will give the records that are in all result sets, so in this case, the tables that have all three columns.

SELECT OBJECT_NAME(object_id) AS Table
FROM sys.columns
WHERE name = 'xColumn1Name'
INTERSECT
SELECT OBJECT_NAME(object_id) AS Table
FROM sys.columns
WHERE name = 'xColumn3Name'
INTERSECT
SELECT OBJECT_NAME(object_id) AS Table
FROM sys.columns
WHERE name = 'xColumn3Name'

Is there a way to search all SQL tables by column name?

3 Answers3