Azure/Transact SQL：动态创建同名表视图或创建后添加表

刚接触Azure SQL的话，遇到这种需要自动适配新增/移除Schema的视图需求确实会有点挠头——毕竟普通的静态视图定义是固定的，没法自动感知Schema或表的变化。不过咱们可以用动态SQL结合存储过程，甚至再加个DDL触发器实现完全自动化，不用依赖外部脚本。

方案1：用存储过程手动更新视图（最易实现） #

这个思路是写一个存储过程，自动查询所有包含Table表的Schema，然后动态拼接UNION ALL语句来创建/替换视图。每次新增或删除Schema后，只要执行一下这个存储过程就行。

存储过程代码示例 #

CREATE OR ALTER PROCEDURE dbo.Refresh_TableView
AS
BEGIN
    SET NOCOUNT ON;

    -- 用来存储最终的视图创建SQL
    DECLARE @viewSQL NVARCHAR(MAX) = N'';

    -- 遍历所有包含`Table`表的Schema，拼接UNION ALL语句
    SELECT @viewSQL += N'UNION ALL SELECT * FROM ' + QUOTENAME(s.name) + N'.[Table] '
    FROM sys.schemas s
    INNER JOIN sys.tables t 
        ON s.schema_id = t.schema_id
    WHERE t.name = N'Table'
      AND s.name != N'Views'; -- 排除存放视图的Views Schema

    -- 去掉开头多余的UNION ALL（第一个SELECT前面不需要）
    SET @viewSQL = STUFF(@viewSQL, 1, 10, N'');

    -- 拼接完整的视图创建语句
    SET @viewSQL = N'CREATE OR ALTER VIEW Views.TableView AS ' + @viewSQL;

    -- 执行动态SQL生成视图
    EXEC sp_executesql @viewSQL;
END;

使用方法 #

当你新增了一个包含Table表的Schema（比如SchemaX），或者删除了某个Schema后，只需要执行：

EXEC dbo.Refresh_TableView;

这个存储过程会自动重新生成Views.TableView，把所有符合条件的表都包含进去。

方案2：用DDL触发器实现自动更新（完全自动化） #

如果不想每次手动执行存储过程，可以加个DDL触发器，监控数据库里的Schema或表创建/删除事件，自动触发视图更新。

DDL触发器代码示例 #

CREATE OR ALTER TRIGGER trg_AutoRefresh_TableView
ON DATABASE
FOR CREATE_SCHEMA, DROP_SCHEMA, CREATE_TABLE, DROP_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    -- 获取触发事件的详细信息
    DECLARE @eventDetails XML = EVENTDATA();
    DECLARE @affectedObjectName NVARCHAR(256) = @eventDetails.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(256)');
    DECLARE @affectedSchemaName NVARCHAR(256) = @eventDetails.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(256)');

    -- 判断是否是和我们的`Table`表相关的操作
    IF (@affectedObjectName = N'Table') 
        OR (@affectedSchemaName IS NOT NULL AND EXISTS (
            SELECT 1 FROM sys.tables t 
            JOIN sys.schemas s ON t.schema_id = s.schema_id 
            WHERE s.name = @affectedSchemaName AND t.name = N'Table'
        ))
    BEGIN
        -- 触发视图更新存储过程
        EXEC dbo.Refresh_TableView;
    END;
END;

作用说明 #

这个触发器会在以下场景自动执行Refresh_TableView存储过程：

• 创建或删除Schema（如果该Schema下存在Table表）
• 创建或删除名为Table的表

这样你新增Schema并创建Table表后，视图会自动更新，完全不用手动干预。

注意事项 #

• 所有Schema下的Table表结构必须完全一致，否则UNION ALL会报错（比如列名、数据类型不匹配）。
• 用QUOTENAME()函数是为了处理带特殊字符的Schema名称（比如包含空格、特殊符号），避免SQL语法错误。
• 确保执行存储过程的账号有足够权限：需要能查询系统视图（sys.schemas、sys.tables），以及创建/修改ViewsSchema下的视图。

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