T-SQL——批量刷新视图

怼烎@ 2024-03-17 09:08 8阅读 0赞

#### 0. 背景说明 ####

为什么要刷新视图？

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

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

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

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

*  使用`EXEC sp_refreshview 'V_XXX';`对视图“V\_XXX”进行刷新操作，
 *  基于原始创建视图的语句，进行`ALTER`操作（MSMS右键视图对象Alter到）

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

--------------------

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

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

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

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

注意这里个视图的作用差不多，但是字段名称不一样。

1.  使用`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');

1.  使用`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. 查看数据库对象间的依赖关系][13.]
    
     *  该文详细的说明的sql server中的对象的依赖关系的查询
 *  [SQL Server 修改表结构后批量更新所有视图][SQL Server]
 *  [T-SQL——批量刷新视图][T-SQL]

[13.]: https://www.cnblogs.com/seusoftware/p/4858115.html
[SQL Server]: https://blog.csdn.net/qq_33397419/article/details/126366079
[T-SQL]: https://www.cnblogs.com/shanzhiming/p/17406141.html