sql server - 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

Answer 1

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

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.