0. 背景說(shuō)明

為什么要刷新視圖？

當(dāng)修改了表結(jié)構(gòu)，比如說(shuō)添加了新字段，之前使用過(guò)該表的視圖則不會(huì)展示新的字段。

即使視圖中是使用*來(lái)獲取該表的所有字段，視圖也不會(huì)獲取到表中新添加的字段。(當(dāng)然也不建議視圖中使用*)

簡(jiǎn)而言之：表結(jié)構(gòu)的更改不會(huì)自動(dòng)的反應(yīng)到已創(chuàng)建的視圖中

因此修改了表結(jié)構(gòu)，需要對(duì)使用該表的視圖進(jìn)行刷新，兩種方式如下：

• 使用EXEC sp_refreshview 'V_XXX';對(duì)視圖“V_XXX”進(jìn)行刷新操作，

• 基于原始創(chuàng)建視圖的語(yǔ)句，進(jìn)行ALTER操作（MSMS右鍵視圖對(duì)象Alter到）

但是很多時(shí)候，并不能快速直接確定那些視圖使用了某個(gè)表，所以需要查詢出依賴該表的所有視圖

1. 查詢出所有使用了指定表的視圖并生成刷新語(yǔ)句

腳本邏輯：使用內(nèi)置的視圖:sys.sql_dependencies

該視圖可以查詢對(duì)象的依賴關(guān)系，該系統(tǒng)視圖支持2005~2016

也可以使用新的依賴查詢視圖：sys.sql_expression_dependencies(2008版本及之后)

注意這里個(gè)視圖的作用差不多，但是字段名稱不一樣。

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');

2. 使用sys.sql_expression_dependencies

--查詢使用了表tbXXX的所有視圖并生成刷新語(yǔ)句
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');

結(jié)果格式如下，比如說(shuō)這里有兩個(gè)視圖使用了tbXXX,則生成兩條sql語(yǔ)句如下

EXEC sp_refreshview 'V_XXX1'
EXEC sp_refreshview 'V_XXX2'

注意：建議使用以上腳本生成刷新語(yǔ)句復(fù)制出來(lái)，手動(dòng)執(zhí)行刷新操作，這樣可以明確自己執(zhí)行的每一條sql語(yǔ)句

2. 創(chuàng)建存儲(chǔ)過(guò)程批量刷新

腳本邏輯：使用內(nèi)置視圖查詢依賴指定的表的所有視圖，然后使用游標(biāo)，將查詢到的視圖一條一條的執(zhí)行刷新操作

-- =============================================
-- Author:		
-- Create date: 2023年5月16日
-- Description:	參數(shù)是表名，用于刷新使用了該表的所有視圖
-- =============================================
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

使用該存儲(chǔ)過(guò)程對(duì)使用了表tbXXX的所有視圖進(jìn)行刷新

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

3. 刷新全部的視圖

腳本邏輯：與上述一樣，使用系統(tǒng)內(nèi)置的對(duì)象視圖，查詢出所有的視圖多像，使用游標(biāo)逐個(gè)進(jìn)行刷新

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) + '個(gè)視圖';

4. 參考

• 13. 查看數(shù)據(jù)庫(kù)對(duì)象間的依賴關(guān)系

  • 該文詳細(xì)的說(shuō)明的sql server中的對(duì)象的依賴關(guān)系的查詢

• SQL Server 修改表結(jié)構(gòu)后批量更新所有視圖

• 表結(jié)構(gòu)改動(dòng)后視圖問(wèn)題