SQL Server - 查找数据库中所有具有唯一ID值的表

Question

我想查找数据库（在SQLServer中）中所有具有特定值“SAM”的列IDName的表，例如IDName ='SAM'。因此，我的初始方法是创建一个具有所有具有列“IDName”的表的表（因为并非数据库中的所有表都具有此列）。然后，我考虑浏览每个表以查看哪些表与IDName ='SAM'匹配-这就是我卡住的地方。我非常确定还有更快的方法来做到这一点，但我对数据库查询编码不太熟悉。任何帮助都会很有用，谢谢！

select * into tmp from
(
SELECT SO.NAME AS TableName, SC.NAME AS ColumnName
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
WHERE sc.name = 'IDName'   and SO.type = 'U'
) tablelist

如果我执行 Select * from tmp，我会得到具有列“IDName”的表列表。现在，我必须逐个检查该列表中的每一个表，并查看它们是否具有“IDName ='Sam'”，如果是，则将其添加到输出表中。最终，我想看到数据库中具有“IDName ='Sam'”的所有表的名称。

- civic.sir

---

如果您愿意使用官方未记录的存储过程，您可以使用sp_MSforeachtable，该存储过程可以接受一个@whereand参数，您可以通过该参数筛选出所需的表。 - Damien_The_Unbeliever

哇，那是一个非常有用的存储过程。今天我学到了。 - Maurice Reeves

抱歉，我不熟悉SQL查询语句，你能告诉我如何编写吗？ - civic.sir

@civic.sir - 这个链接将带您转到另一个问题/答案，展示了如何使用它。 - Damien_The_Unbeliever

Answer 1

DECLARE @sql AS varchar(max) = '';
DECLARE @ColumnName AS varchar(100) = 'IDName'
DECLARE @ResultQuery AS varchar(max) =  'SELECT ''@TableName'' AS TableName ' + 
                                        '       ,@ColumnName ' +
                                        'FROM @TableName ' +
                                        'WHERE @ColumnName = ''SAM''';
SET @ResultQuery = REPLACE(@ResultQuery, '@ColumnName', QUOTENAME(@ColumnName));

WITH AllTables AS (
    SELECT SCHEMA_NAME(Tables.schema_id) AS SchemaName
          ,Tables.name AS TableName
          ,Columns.name AS ColumnName
    FROM sys.tables AS Tables
        INNER JOIN sys.columns AS Columns 
            ON Tables.object_id = Columns.object_id
    WHERE Columns.name = @ColumnName
)
SELECT @sql = @sql + ' UNION ALL ' +
       REPLACE(@ResultQuery, '@TableName', QUOTENAME(TableName)) + CHAR(13)
FROM AllTables

SET @sql = STUFF(@sql, 1, LEN(' UNION ALL'), '');

--PRINT @sql;

SET @sql = 
'WITH AllTables AS ( ' +
   @sql + 
') ' +
'SELECT DISTINCT TableName ' +
'FROM AllTables ';

EXEC (@sql)

- adrianm

---

太棒了！这正是我在寻找的。非常感谢@adrianm!! - civic.sir

很棒的答案，但如果架构不是dbo，它将失败，最好将替换行更改为：REPLACE(@ResultQuery, '@TableName', QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)) + CHAR(13) - YazX