SQL Server含UNION的视图：如何程序化提取列关联元数据？

核心结论 #

可以通过解析视图的定义文本，程序化提取每个UNION分支的列映射关系、数据类型及序号信息，自动填充到你的元数据表中。

解决思路 #

系统DMF（如sys.dm_sql_referenced_entities）无法区分视图中独立计算列（如CAST(NULL AS INT)）与源表引用列的关联关系，因此需要直接解析视图的CREATE语句文本：

1. 从sys.sql_modules获取视图的完整定义文本
2. 拆分文本中的每个SELECT块（以UNION ALL为分隔符）
3. 对每个SELECT块，解析列的序号、别名、源表达式（区分直接列引用和计算列）
4. 提取计算列的显式数据类型，或从源表列获取数据类型
5. 将解析结果映射到你的元数据表结构中

实现脚本示例 #

以下是针对SQL Server环境的示例脚本，可解析指定视图的元数据并插入到你的元数据表中：

1. 辅助函数：拆分字符串（用于拆分UNION块） #

CREATE FUNCTION dbo.SplitString
(
    @String NVARCHAR(MAX),
    @Delimiter NVARCHAR(50)
)
RETURNS @Result TABLE (Value NVARCHAR(MAX), Ordinal INT)
AS
BEGIN
    DECLARE @Ordinal INT = 1;
    DECLARE @StartIndex INT = 1;
    DECLARE @EndIndex INT;

    WHILE CHARINDEX(@Delimiter, @String, @StartIndex) > 0
    BEGIN
        SET @EndIndex = CHARINDEX(@Delimiter, @String, @StartIndex);
        INSERT INTO @Result (Value, Ordinal)
        VALUES (LTRIM(RTRIM(SUBSTRING(@String, @StartIndex, @EndIndex - @StartIndex))), @Ordinal);
        SET @StartIndex = @EndIndex + LEN(@Delimiter);
        SET @Ordinal = @Ordinal + 1;
    END

    INSERT INTO @Result (Value, Ordinal)
    VALUES (LTRIM(RTRIM(SUBSTRING(@String, @StartIndex, LEN(@String) - @StartIndex + 1))), @Ordinal);
    RETURN;
END
GO

2. 解析视图元数据的主存储过程 #

CREATE PROCEDURE dbo.ExtractCoveringViewMetadata
    @ViewSchema NVARCHAR(128),
    @ViewName NVARCHAR(128)
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取视图定义文本，清理换行/制表符格式
    DECLARE @ViewDefinition NVARCHAR(MAX);
    SELECT @ViewDefinition = REPLACE(REPLACE(REPLACE(m.definition, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ')
    FROM sys.sql_modules m
    JOIN sys.views v ON m.object_id = v.object_id
    JOIN sys.schemas s ON v.schema_id = s.schema_id
    WHERE s.name = @ViewSchema AND v.name = @ViewName;

    -- 拆分每个SELECT块（去掉CREATE VIEW ... AS前缀）
    DECLARE @SelectBlocks TABLE (BlockText NVARCHAR(MAX), BlockOrdinal INT);
    INSERT INTO @SelectBlocks
    SELECT Value, Ordinal
    FROM dbo.SplitString(SUBSTRING(@ViewDefinition, CHARINDEX('AS ', @ViewDefinition) + 3, LEN(@ViewDefinition)), 'UNION ALL');

    -- 初始化视图主记录
    DECLARE @ViewId INT;
    SELECT @ViewId = Id FROM info.CoveringView WHERE VIEW_SCHEMA = @ViewSchema AND VIEW_NAME = @ViewName;
    IF @ViewId IS NULL
    BEGIN
        INSERT INTO info.CoveringView (VIEW_SCHEMA, VIEW_NAME)
        VALUES (@ViewSchema, @ViewName);
        SET @ViewId = SCOPE_IDENTITY();
    END

    -- 遍历每个SELECT块
    DECLARE @BlockOrdinal INT, @BlockText NVARCHAR(MAX);
    DECLARE BlockCursor CURSOR FOR
        SELECT BlockText, BlockOrdinal FROM @SelectBlocks;
    OPEN BlockCursor;
    FETCH NEXT FROM BlockCursor INTO @BlockText, @BlockOrdinal;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 提取当前块关联的源表信息
        DECLARE @TableSchema NVARCHAR(128), @TableName NVARCHAR(128);
        DECLARE @FromClause NVARCHAR(MAX) = SUBSTRING(@BlockText, CHARINDEX('FROM ', @BlockText) + 5, LEN(@BlockText));
        SET @FromClause = LEFT(@FromClause, CHARINDEX(' ', @FromClause + ' ') - 1);
        IF CHARINDEX('.', @FromClause) > 0
        BEGIN
            SET @TableSchema = LEFT(@FromClause, CHARINDEX('.', @FromClause) - 1);
            SET @TableName = SUBSTRING(@FromClause, CHARINDEX('.', @FromClause) + 1, LEN(@FromClause));
        END
        ELSE
        BEGIN
            SET @TableSchema = @ViewSchema;
            SET @TableName = @FromClause;
        END

        -- 提取SELECT子句中的列列表
        DECLARE @SelectClause NVARCHAR(MAX) = SUBSTRING(@BlockText, CHARINDEX('SELECT ', @BlockText) + 7, CHARINDEX(' FROM ', @BlockText) - (CHARINDEX('SELECT ', @BlockText) + 7));
        DECLARE @Columns TABLE (ColumnText NVARCHAR(MAX), ColumnOrdinal INT);
        INSERT INTO @Columns
        SELECT Value, Ordinal
        FROM dbo.SplitString(@SelectClause, ',');

        -- 解析每个列的详细信息
        DECLARE @ColumnOrdinal INT, @ColumnText NVARCHAR(MAX);
        DECLARE ColumnCursor CURSOR FOR
            SELECT ColumnText, ColumnOrdinal FROM @Columns;
        OPEN ColumnCursor;
        FETCH NEXT FROM ColumnCursor INTO @ColumnText, @ColumnOrdinal;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @ColumnText = LTRIM(RTRIM(@ColumnText));
            DECLARE @ViewColumnName NVARCHAR(128), @SourceColumn NVARCHAR(128), @DataType NVARCHAR(128);

            -- 处理带别名的列（如Financial_Year AS FINANCIAL_YEAR）
            IF CHARINDEX(' AS ', @ColumnText) > 0
            BEGIN
                SET @ViewColumnName = SUBSTRING(@ColumnText, CHARINDEX(' AS ', @ColumnText) + 4, LEN(@ColumnText));
                SET @ViewColumnName = REPLACE(REPLACE(@ViewColumnName, '[', ''), ']', '');
                DECLARE @SourceExpr NVARCHAR(MAX) = LEFT(@ColumnText, CHARINDEX(' AS ', @ColumnText) - 1);
                SET @SourceExpr = LTRIM(RTRIM(@SourceExpr));

                -- 区分计算列与直接列引用
                IF LEFT(@SourceExpr, 5) = 'CAST('
                BEGIN
                    -- 提取CAST语句中的显式数据类型
                    SET @DataType = SUBSTRING(@SourceExpr, CHARINDEX(' AS ', @SourceExpr) + 4, CHARINDEX(')', @SourceExpr) - (CHARINDEX(' AS ', @SourceExpr) + 4));
                    SET @SourceColumn = NULL;
                END
                ELSE
                BEGIN
                    -- 直接引用源表列，从系统视图获取数据类型
                    SET @SourceColumn = REPLACE(REPLACE(@SourceExpr, '[', ''), ']', '');
                    SELECT @DataType = CONCAT(t.name, CASE WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'decimal', 'numeric') THEN CONCAT('(', CASE WHEN t.name IN ('decimal', 'numeric') THEN CONCAT(c.precision, ',', c.scale) ELSE c.max_length END, ')') ELSE '' END)
                    FROM sys.columns c
                    JOIN sys.tables tbl ON c.object_id = tbl.object_id
                    JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id
                    JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
                    WHERE sch.name = @TableSchema AND tbl.name = @TableName AND c.name = @SourceColumn;
                END
            END
            ELSE
            BEGIN
                -- 无别名，列名与源列一致
                SET @ViewColumnName = REPLACE(REPLACE(@ColumnText, '[', ''), ']', '');
                SET @SourceColumn = @ViewColumnName;
                SELECT @DataType = CONCAT(t.name, CASE WHEN t.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'decimal', 'numeric') THEN CONCAT('(', CASE WHEN t.name IN ('decimal', 'numeric') THEN CONCAT(c.precision, ',', c.scale) ELSE c.max_length END, ')') ELSE '' END)
                FROM sys.columns c
                JOIN sys.tables tbl ON c.object_id = tbl.object_id
                JOIN sys.schemas sch ON tbl.schema_id = sch.schema_id
                JOIN sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id
                WHERE sch.name = @TableSchema AND tbl.name = @TableName AND c.name = @SourceColumn;
            END

            -- 插入/更新CoveringViewColumns记录
            DECLARE @ColumnId INT;
            SELECT @ColumnId = Id FROM info.CoveringViewColumns WHERE CoveringViewId = @ViewId AND VIEW_COLUMN_NAME = @ViewColumnName AND ORDINAL_POSITION = @ColumnOrdinal;
            IF @ColumnId IS NULL
            BEGIN
                INSERT INTO info.CoveringViewColumns (CoveringViewId, VIEW_COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE_AND_SIZE)
                VALUES (@ViewId, @ViewColumnName, @ColumnOrdinal, @DataType);
                SET @ColumnId = SCOPE_IDENTITY();
            END

            -- 插入CoveringViewColumnsTableColumns记录（仅针对源列引用）
            IF @SourceColumn IS NOT NULL
            BEGIN
                IF NOT EXISTS (SELECT 1 FROM info.CoveringViewColumnsTableColumns WHERE CoveringViewColumnsId = @ColumnId AND TABLE_SCHEMA = @TableSchema AND TABLE_NAME = @TableName AND TABLE_COLUMN_NAME = @SourceColumn)
                BEGIN
                    INSERT INTO info.CoveringViewColumnsTableColumns (CoveringViewColumnsId, TABLE_SCHEMA, TABLE_NAME, TABLE_COLUMN_NAME)
                    VALUES (@ColumnId, @TableSchema, @TableName, @SourceColumn);
                END
            END

            FETCH NEXT FROM ColumnCursor INTO @ColumnText, @ColumnOrdinal;
        END
        CLOSE ColumnCursor;
        DEALLOCATE ColumnCursor;

        FETCH NEXT FROM BlockCursor INTO @BlockText, @BlockOrdinal;
    END
    CLOSE BlockCursor;
    DEALLOCATE BlockCursor;
END
GO

3. 使用示例 #

针对你的示例视图，执行以下命令即可提取元数据：

EXEC dbo.ExtractCoveringViewMetadata @ViewSchema = 'info', @ViewName = 'vw_ActivitySummary';

执行后，你的info.CoveringViewSpecifications视图将包含完整的元数据信息，可直接用于生成动态覆盖视图。

注意事项 #

• 脚本基于SQL Server环境编写，若使用其他数据库需调整系统视图和字符串处理逻辑
• 复杂计算列（如多函数嵌套、表达式组合）的解析需进一步扩展逻辑，当前脚本仅处理CAST(NULL AS ...)和直接列引用的场景
• 建议先在测试环境验证脚本，确保解析结果符合预期后再批量处理30-40个视图

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