如何从SQL Server视图中获取原始列名与列别名

需求说明 #

需要从目标视图中提取每个字段的原始引用列名（如Cmpy_Cod、K00001）和AS指定的列别名（如Machn_Cod、Machn_Desc），示例视图的SQL定义如下：

SELECT 
      [Cmpy_Cod]
      , TRIM( SUBSTRING( [K00001], 5, 6 ) ) AS [Machn_Cod]
      , TRIM( SUBSTRING( [F00002], 9, 20 ) ) AS [Machn_Desc]
      , TRIM( SUBSTRING( [K00001], 5, 3 ) ) AS [Machn_WC_Cod]
      , CASE
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '111111' THEN 'AAA'
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '222222' THEN 'BBB'
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '333333' THEN 'CCC'
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '444444' THEN 'DDD'
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '555555' THEN 'EEE'
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '666666' THEN 'FFF'
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '777777' THEN 'GGG'
        WHEN TRIM( SUBSTRING( [K00001], 5, 6 ) ) = '888888' THEN 'HHH'
        ELSE 'N/A'
        END 
        AS [Machn_Dept_Cod]
FROM
    "TABLE_NAME"

期望得到的结果示例：

解决方案 #

方法1：系统视图关联查询 #

使用SQL Server内置系统视图，直接关联视图列定义与依赖的原始列，执行以下查询（替换你的视图名称为实际视图名）：

SELECT
    c.name AS 列别名,
    col.name AS 原始列名
FROM
    sys.views v
JOIN
    sys.columns c ON v.object_id = c.object_id
LEFT JOIN
    sys.sql_expression_dependencies sed 
        ON v.object_id = sed.referencing_id 
        AND c.column_id = sed.referencing_minor_id
LEFT JOIN
    sys.columns col 
        ON sed.referenced_id = col.object_id 
        AND sed.referenced_minor_id = col.column_id
WHERE
    v.name = '你的视图名称'
ORDER BY
    c.column_id;

该查询可直接返回每个列别名对应的原始引用列，对于函数、CASE表达式这类基于原始列的计算字段，也能准确关联到其依赖的原始列。

方法2：提取视图定义解析（复杂场景） #

如果需要获取完整的字段表达式文本（如TRIM(SUBSTRING([K00001],5,6))），先查询视图的完整定义：

SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('你的视图名称');

之后可通过字符串处理或正则表达式解析每个字段的表达式和别名。注意正则解析需适配SQL语法的换行、嵌套函数等复杂情况，示例正则（仅供参考）：

(.*?)\s+AS\s+\[?(\w+)\]?,?

注意事项 #

• 执行查询的账号需拥有VIEW DEFINITION权限，否则无法访问系统视图元数据。
• 对于跨库、跨服务器的引用列，需扩展OBJECT_NAME函数，添加对应的数据库或服务器名称。

内容的提问来源于stack exchange，提问作者Filippo Costa