T-SQL——批量刷新视图

1. 查询出所有使用了指定表的视图并生成刷新语句
2. 创建存储过程批量刷新
3. 刷新全部的视图
4. 参考

shanzm——2023年5月16日

0. 背景说明

当修改了表结构，比如说添加了新字段，之前使用过该表的视图则不会展示新的字段。

*来获取该表的所有字段，视图也不会获取到表中新添加的字段。(当然也不建议视图中使用*

表结构的更改不会自动的反应到已创建的视图中

使用EXEC sp_refreshview 'V_XXX';对视图“V_XXX”进行刷新操作，

ALTER操作（MSMS右键视图对象Alter到）

1. 查询出所有使用了指定表的视图并生成刷新语句

脚本逻辑：使用内置的视图:sys.sql_dependencies

也可以使用新的依赖查询视图：sys.sql_expression_dependencies(2008版本及之后

sys.sql_dependencies

SELECT DISTINCT
       'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so
    INNER JOIN sys.sql_dependencies sd
        ON so.object_id = sd.object_id
WHERE type = 'V'
      AND sd.referenced_major_id = OBJECT_ID(N'tb';

使用sys.sql_expression_dependencies

--查询使用了表tbXXX的所有视图并生成刷新语句
SELECT DISTINCT
       'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so
    INNER JOIN sys.sql_expression_dependencies sd
        ON so.object_id = sd.referencing_id
WHERE type = 'V'
      AND sd.referenced_id = OBJECT_ID(N'tbXXX';

结果格式如下，比如说这里有两个视图使用了tbXXX,则生成两条sql语句如下

EXEC sp_refreshview 'V_XXX1'
EXEC sp_refreshview 'V_XXX2'

注意：建议使用以上脚本生成刷新语句复制出来，手动执行刷新操作，这样可以明确自己执行的每一条sql语句

2. 创建存储过程批量刷新

-- =============================================
-- Author:		
-- Create date: 2023年5月16日
-- Description:	参数是表名，用于刷新使用了该表的所有视图
-- =============================================
CREATE PROCEDURE [dbo].[pro_RefreshView] @table_name NVARCHAR(200
AS
BEGIN

    DECLARE MyCursor CURSOR FOR
    SELECT OBJECT_NAME(referencing_id AS referencing_entity_name
    FROM sys.sql_expression_dependencies AS sed
        INNER JOIN sys.objects AS o
            ON sed.referencing_id = o.object_id
    WHERE referenced_id = OBJECT_ID(N'' + @table_name + ''
          AND o.type_desc = 'VIEW';

    DECLARE @view_name VARCHAR(40;
    OPEN MyCursor;

    FETCH NEXT FROM MyCursor
    INTO @view_name;
    WHILE (@@fetch_status <> -1
    BEGIN
        IF (@@fetch_status <> -2
        BEGIN
            PRINT @view_name;--打印出操作的视图名称
            EXEC sp_refreshview @view_name;
        END;

        FETCH NEXT FROM MyCursor
        INTO @view_name;
    END;

    CLOSE MyCursor;
    DEALLOCATE MyCursor;
END;
GO

使用该存储过程对使用了表tbXXX的所有视图进行刷新

EXEC dbo.pro_RefreshView @table_name = N'tb' -- nvarchar(200

3. 刷新全部的视图

脚本逻辑：与上述一样，使用系统内置的对象视图，查询出所有的视图多像，使用游标逐个进行刷新

DECLARE @ViewName VARCHAR(250;
DECLARE @i INT;
SET @i = 0;
DECLARE #_cursor CURSOR FOR SELECT name FROM sys.sysobjects WHERE type = 'V';

OPEN #_cursor;

FETCH NEXT FROM #_cursor
INTO @ViewName;

WHILE @@fetch_status = 0
BEGIN
    PRINT '成功刷新视图: ' + @ViewName;
    EXEC sp_refreshview @ViewName;
    SET @i = @i + 1;
    FETCH NEXT FROM #_cursor
    INTO @ViewName;
END;

CLOSE #_cursor;
DEALLOCATE #_cursor;
PRINT '完成';
PRINT '共成功刷新' + CONVERT(VARCHAR(10, @i + '个视图';

4. 参考

13. 查看数据库对象间的依赖关系

该文详细的说明的sql server中的对象的依赖关系的查询

表结构改动后视图问题

编程笔记 » T-SQL——批量刷新视图