有没有一种方法可以按列名搜索所有SQL表

Question

在这个答案中，您可以按列名搜索所有表中的列。

假设我有一个这样的列列表：

DECLARE @columnNames TABLE (Id varchar(30))
INSERT INTO @columnNames 
VALUES ('xColumn1Name'), ('xColumn2Name'), ('xColumn3Name')

我想找到所有至少有这三列的表。有没有可能用下面的代码进行foreach循环，或者有没有更简单的方法？

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;

该表必须有列表中命名的所有3列，如果我能过滤掉没有特定列或列列表的表，那将是很酷的

Answer 1

这是一个关系除法问题。正如乔·塞尔科所写，有几种方法可以解决这个问题。常见的解决方案如下：

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);

这意味着：给我所有的表，其中与列表@columnName匹配的列数与该列表中的列数相同或更多，换句话说，tehre是每个列的匹配项。

Answer 2

这应该是您的初始目标。

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

为了得到简短的解释，此查询将在信息模式中查找表中的任何列。ROW_NUMBER((然后基本上按表对列进行分组。如果有3个或更多的结果(rn(，那么所有3列都在那里。

由于它是一个子选择，如果需要，还可以为特定列筛选外部选择。

Answer 3

您可以使用INTERSECT来组合不同的结果集。这将给出所有结果集中的记录，因此在本例中，给出具有所有三列的表。

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'