目录0. 背景说明1. 查询出所有使用了指定表的视图并生成刷新语句2. 创建存储过程批量刷新3. 刷新全部的视图4. 参考shanzm——2023年5月16日

---

0. 背景说明

为什么要刷新视图？

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

【资料图】

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

简而言之：表结构的更改不会自动的反应到已创建的视图中

因此修改了表结构，需要对使用该表的视图进行刷新，两种方式如下：

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

基于原始创建视图的语句，进行ALTER操作（MSMS右键视图对象Alter到）

但是很多时候，并不能快速直接确定那些视图使用了某个表，所以需要查询出依赖该表的所有视图

---

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

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

该视图可以查询对象的依赖关系，该系统视图支持2005~2016

也可以使用新的依赖查询视图：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_idWHERE 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_idWHERE 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)ASBEGIN    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 #_cursorINTO @ViewName;WHILE @@fetch_status = 0BEGIN    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中的对象的依赖关系的查询

SQL Server 修改表结构后批量更新所有视图

表结构改动后视图问题

关键词：