如何在SQL Server中定位使用指定列的所有视图与函数

针对你遇到的模糊列名匹配、别名提取、依赖层级区分三个核心问题，以下是适配SQL Server 2019的实操方案：

核心解决方案脚本 #

先定义目标列和主表名，再通过多层CTE实现精确匹配、别名提取与依赖链追踪：

-- 替换为你要排查的目标列和主表名
DECLARE @TargetColumnName NVARCHAR(100) = 'MSTFLG14';
DECLARE @BaseTableName NVARCHAR(100) = '你的主表名';

WITH ObjectUsesColumn AS (
    -- 精确匹配使用目标列的视图/函数，避免匹配MSTFLG10/MSTFLG11这类无关列
    SELECT
        o.object_id,
        o.name AS object_name,
        o.type_desc,
        m.definition,
        PATINDEX('%[^a-zA-Z0-9]' + @TargetColumnName + '[^a-zA-Z0-9]%', m.definition) AS match_pos
    FROM sys.sql_modules m
    JOIN sys.objects o ON o.object_id = m.object_id
    WHERE 
        PATINDEX('%[^a-zA-Z0-9]' + @TargetColumnName + '[^a-zA-Z0-9]%', m.definition) > 0
        AND o.type IN ('V', 'FN', 'IF', 'TF') -- 仅筛选视图、函数类型
),
AliasExtraction AS (
    -- 提取列的别名（处理AS 别名/AS [别名]两种格式）
    SELECT
        object_id,
        object_name,
        type_desc,
        TRIM(
            REPLACE(
                REPLACE(
                    SUBSTRING(
                        definition,
                        CHARINDEX('AS', definition, match_pos) + 2,
                        CHARINDEX(CHAR(10), definition, CHARINDEX('AS', definition, match_pos)) - (CHARINDEX('AS', definition, match_pos) + 2)
                    ),
                    '[', ''
                ),
                ']', ''
            )
        ) AS column_alias
    FROM ObjectUsesColumn
    WHERE CHARINDEX('AS', definition, match_pos) > 0
    -- 补充无别名的场景（比如函数直接引用列）
    UNION ALL
    SELECT
        object_id,
        object_name,
        type_desc,
        NULL AS column_alias
    FROM ObjectUsesColumn
    WHERE CHARINDEX('AS', definition, match_pos) = 0
),
DependencyChain AS (
    -- 递归追踪依赖链，区分直接/间接依赖
    SELECT
        ae.object_id,
        ae.object_name,
        ae.type_desc,
        ae.column_alias,
        @BaseTableName AS referenced_object,
        '直接依赖' AS dependency_type,
        1 AS dependency_level
    FROM AliasExtraction ae
    JOIN sys.sql_expression_dependencies sed ON ae.object_id = sed.referencing_id
    JOIN sys.objects ref_obj ON sed.referenced_id = ref_obj.object_id
    WHERE ref_obj.name = @BaseTableName

    UNION ALL

    SELECT
        ae.object_id,
        ae.object_name,
        ae.type_desc,
        ae.column_alias,
        dc.referenced_object,
        '间接依赖' AS dependency_type,
        dc.dependency_level + 1 AS dependency_level
    FROM AliasExtraction ae
    JOIN sys.sql_expression_dependencies sed ON ae.object_id = sed.referencing_id
    JOIN DependencyChain dc ON sed.referenced_id = dc.object_id
    WHERE ae.object_id NOT IN (SELECT object_id FROM DependencyChain)
)
-- 输出最终结果，去重并按依赖层级排序
SELECT DISTINCT
    object_name AS 对象名称,
    type_desc AS 对象类型,
    column_alias AS 列别名,
    dependency_type AS 依赖类型,
    dependency_level AS 依赖层级
FROM DependencyChain
ORDER BY dependency_level, type_desc, object_name;

脚本说明 #

1. 精确匹配列名：
   用PATINDEX配合[^a-zA-Z0-9]匹配单词边界，确保仅匹配独立的目标列名，避免MSTFLG1被误匹配为MSTFLG10/MSTFLG11等。

2. 提取列别名：
   解析对象定义中的AS关键字，截取并清理别名文本，兼容普通别名和带方括号的别名格式；无别名的场景返回NULL。

3. 区分依赖层级：
   通过递归CTE构建依赖链：

   • 直接依赖：对象直接关联主表，层级为1
   • 间接依赖：对象通过其他视图/函数关联主表，层级大于1，数字越大表示依赖链越长

注意事项 #

• 对于使用SELECT *的视图，先执行sp_refreshview '视图名'刷新依赖，否则sys.sql_expression_dependencies无法准确追踪列级依赖。
• 若函数逻辑复杂（比如嵌套引用），可根据实际情况调整别名提取的字符串解析规则。
• 针对大量对象的场景，可添加AND o.schema_id = SCHEMA_ID('你的Schema名')缩小查询范围，提升性能。

内容的提问来源于stack exchange，提问作者Jinu Joseph