修改表列后，如何批量或自动更新关联SQL函数？

假设存在包含column1和column2两列的Table1表，基于该表创建了如下表值函数：

CREATE OR ALTER Function [dbo].[Fn_Test] ()
RETURNS TABLE
AS
    RETURN
        SELECT *
        FROM Table1

当后续为该表新增或删除列时，上述函数不会自动同步更新，需手动修改。现需解决如何批量更新所有关联函数，或实现函数随表列变更自动更新的问题。

一、自动同步：通过DDL触发器实现表结构变更时自动刷新关联函数 #

可以创建DDL触发器，当Table1发生列增删操作时，自动刷新所有依赖它的表值函数元数据，实现结构同步。

触发器脚本 #

CREATE TRIGGER Trigger_Refresh_Functions_On_Table1_Change
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    -- 仅针对dbo.Table1的修改操作执行
    IF EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)') = 'Table1'
       AND EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)') = 'dbo'
    BEGIN
        -- 遍历所有依赖Table1的表值函数并刷新元数据
        DECLARE @FunctionName NVARCHAR(512);
        DECLARE FuncCursor CURSOR FOR
            SELECT QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
            FROM sys.objects o
            JOIN sys.schemas s ON o.schema_id = s.schema_id
            JOIN sys.sql_expression_dependencies d ON o.object_id = d.referencing_id
            JOIN sys.objects referenced_obj ON d.referenced_id = referenced_obj.object_id
            WHERE o.type IN ('IF', 'TF') -- IF=内联表值函数，TF=多语句表值函数
              AND referenced_obj.name = 'Table1'
              AND referenced_obj.schema_id = SCHEMA_ID('dbo');

        OPEN FuncCursor;
        FETCH NEXT FROM FuncCursor INTO @FunctionName;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC sys.sp_refreshsqlmodule @FunctionName;
            FETCH NEXT FROM FuncCursor INTO @FunctionName;
        END

        CLOSE FuncCursor;
        DEALLOCATE FuncCursor;
    END
END
GO

二、批量更新现有关联函数 #

如果已经存在一批需要同步的函数，可直接执行以下脚本批量刷新：

DECLARE @FunctionName NVARCHAR(512);
DECLARE FuncCursor CURSOR FOR
    SELECT QUOTENAME(s.name) + '.' + QUOTENAME(o.name)
    FROM sys.objects o
    JOIN sys.schemas s ON o.schema_id = s.schema_id
    JOIN sys.sql_expression_dependencies d ON o.object_id = d.referencing_id
    JOIN sys.objects referenced_obj ON d.referenced_id = referenced_obj.object_id
    WHERE o.type IN ('IF', 'TF')
      AND referenced_obj.name = 'Table1'
      AND referenced_obj.schema_id = SCHEMA_ID('dbo');

OPEN FuncCursor;
FETCH NEXT FROM FuncCursor INTO @FunctionName;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Refreshing function: ' + @FunctionName;
    EXEC sys.sp_refreshsqlmodule @FunctionName;
    FETCH NEXT FROM FuncCursor INTO @FunctionName;
END

CLOSE FuncCursor;
DEALLOCATE FuncCursor;
GO

三、预防建议：避免在函数中使用SELECT * #

从SQL开发最佳实践角度，建议在表值函数中明确指定返回列，而非使用SELECT *：

• 避免意外引入无关列，提升代码可读性
• 表结构变更时，可更精准地控制函数返回结构
• 减少元数据缓存带来的同步问题

示例修改后的函数：

CREATE OR ALTER Function [dbo].[Fn_Test] ()
RETURNS TABLE
AS
    RETURN
        SELECT column1, column2 -- 新增列时手动添加到此处
        FROM Table1
GO

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