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

Question

我想找出数据库(在 SQLServer 中)中 IDName 列具有特定值“SAM”的所有表，例如 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”的数据库中所有表的名称。

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)