SQL Server中如何查询存在指定列的所有数据库及对应表

实现思路 #

• 调用系统存储过程sp_MSforeachdb遍历实例下所有用户数据库
• 单个数据库内通过sys.tables、sys.columns系统视图匹配指定列名
• 默认排除master、model、msdb、tempdb四个系统库，可根据需求自行调整过滤规则

完整查询代码 #

-- 替换此处变量值为你要查找的目标列名
DECLARE @TargetColumnName NVARCHAR(128) = N'需要查询的列名';

EXEC sp_MSforeachdb N'
USE [?];
-- 不需要排除系统库可删除下一行IF判断
IF DB_ID() > 4
BEGIN
    SELECT 
        ''?'' AS 数据库名,
        t.name AS 表名,
        c.name AS 匹配列名,
        ty.name AS 列数据类型
    FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id = c.object_id
    INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id
    WHERE c.name = @TargetColumnName
END
', @params1 = N'@TargetColumnName NVARCHAR(128)', @TargetColumnName = @TargetColumnName;

注意事项 #

• sp_MSforeachdb是SQL Server未公开的系统存储过程，存在已知的偶发漏扫描数据库的问题，如果实例数据库数量较多、对准确性要求高，建议替换为游标遍历sys.databases的方案更稳定
• 执行查询的账号需要具备所有目标数据库的系统视图查看权限，否则会出现权限报错或返回空结果
• 如果需要扫描系统库内的表，删除代码中的IF DB_ID() > 4判断即可

内容的提问来源于stack exchange，提问作者Eliseo Jr