[SQL Server] Table新增欄位, 但對應的 View 卻沒有異動的處理方式 (sp_refreshview) (進階) : 取出全部相依物件 with CTE, Recursive

0.. 前言

接續前一篇文章, 本文要來探討如何取出某個 Table 的全部相依物件 (View), 進行 refresh View 的動作.
茲分為以下幾個部份及實作步驟進行說明:

1.. 問題重現
2.. 解決方式
3.. 延伸閱讀 (2018.06.25 補充)
4.. 結論
5.. 參考文件

1.. 問題重現

(1) 呈現前一篇文章的最後狀況

-- ===========================
-- 切換使用中的資料庫
-- ===========================
USE Cookies;
GO

-- ===========================
-- 前一篇文章的最後狀況 
-- ===========================
SELECT *
FROM   [dbo].[vwProducts]
;
GO
-- OUTPUT
-- Id Name   Category Price ValidDay CategoryName
-- 1 夏威夷豆塔  1   50  14   餅乾類
-- 2 堅果塔   1   55  14   餅乾類
-- 3 養生堅果  1   60  14   餅乾類
-- 4 乳酪塔   1   60  14   餅乾類
-- 5 檸檬塔   1   60  14   餅乾類
-- 6 草莓塔   1   65  14   餅乾類
-- 7 輕乳酪蛋糕(片) 2   65  14   蛋糕類
-- 8 重乳酪蛋糕(片) 2   70  14   蛋糕類
-- 9 抹茶奶酪  3   90  14   奶酪類
-- 10 草莓奶酪  3   85  14   奶酪類
-- 11 芒果奶酪  3   85  14   奶酪類
-- 12 紅豆奶酪  3   80  14   奶酪類

(2) 新增2個 View

-- ===========================
-- 移除 View 
-- ===========================
DROP VIEW  IF EXISTS [dbo].[vwProductsCookies];
GO
DROP VIEW  IF EXISTS [dbo].[vwProductsCake];
GO

-- ===========================
-- 建立 View
-- ===========================
CREATE VIEW [dbo].[vwProductsCookies]
AS
SELECT  V.*
FROM [vwProducts] V
WHERE  V.Category = 1
;
GO
CREATE VIEW [dbo].[vwProductsCake]
AS
SELECT  V.*
FROM [vwProducts] V
WHERE  V.Category = 2
;
GO

-- ===========================
-- 查詢一下
-- ===========================
SELECT *
FROM vwProductsCookies
;
-- OUTPUT
-- Id Name  Category Price ValidDay CategoryName
-- 1 夏威夷豆塔 1   50  14   餅乾類
-- 2 堅果塔  1   55  14   餅乾類
-- 3 養生堅果 1   60  14   餅乾類
-- 4 乳酪塔  1   60  14   餅乾類
-- 5 檸檬塔  1   60  14   餅乾類
-- 6 草莓塔  1   65  14   餅乾類

SELECT *
FROM vwProductsCake
;
-- Id Name   Category Price ValidDay CategoryName
-- 7 輕乳酪蛋糕(片) 2   65  14   蛋糕類
-- 8 重乳酪蛋糕(片) 2   70  14   蛋糕類

(3) 在 table:Products 加入欄位

-- ===========================
-- 在 TABLE: Products 加上欄位 
-- ===========================
-- 加入 宅配方式 : 1-常溫 2-冷藏 3-冷凍
ALTER TABLE [dbo].[Products]
ADD [ShipMethod]  INT  NULL
;
GO

UPDATE [dbo].[Products]
SET [ShipMethod] = 1
WHERE  [Category] = 1
;
GO

UPDATE [dbo].[Products]
SET [ShipMethod] = 2
WHERE  [Category] = 2
;
GO

UPDATE [dbo].[Products]
SET [ShipMethod] = 3
WHERE  [Category] = 3
;
GO

ALTER TABLE [dbo].[Products]
ALTER COLUMN [ShipMethod]  INT  NOT NULL
;
GO

(4) 當然, 此時 SELECT vwProducts, vwProductsCookies, vwProductsCake 都會出現前一篇所述的狀況; CategoryName 的值, 變成新加入的 ShipMethod

-- ===========================
-- 查詢一下
-- ===========================
SELECT *
FROM vwProducts
;
-- OUTPUT
-- Id Name   Category Price ValidDay CategoryName
-- 1 夏威夷豆塔  1   50  14   1
-- 2 堅果塔   1   55  14   1
-- 3 養生堅果  1   60  14   1
-- 4 乳酪塔   1   60  14   1
-- 5 檸檬塔   1   60  14   1
-- 6 草莓塔   1   65  14   1
-- 7 輕乳酪蛋糕(片) 2   65  14   2
-- 8 重乳酪蛋糕(片) 2   70  14   2
-- 9 抹茶奶酪  3   90  14   3
-- 10 草莓奶酪  3   85  14   3
-- 11 芒果奶酪  3   85  14   3
-- 12 紅豆奶酪  3   80  14   3

SELECT *
FROM vwProductsCookies
;
-- OUTPUT
-- Id Name   Category Price ValidDay CategoryName
-- 1 夏威夷豆塔  1   50  14   1
-- 2 堅果塔   1   55  14   1
-- 3 養生堅果  1   60  14   1
-- 4 乳酪塔   1   60  14   1
-- 5 檸檬塔   1   60  14   1
-- 6 草莓塔   1   65  14   1

SELECT *
FROM vwProductsCake
;
-- Id Name   Category Price ValidDay CategoryName
-- 7 輕乳酪蛋糕(片) 2   65  14   2
-- 8 重乳酪蛋糕(片) 2   70  14   2

(5) 如今, 我們的狀況, 不再是只有1層相依, 即 vwProducts 相依於 table:Products; 而是以下這種方式:
level 0: table:Products -->
level 1: view: vwProducts -->
level 2: view: vwProductsCookies / view: vwProductsCake
因此, 我們要找另一個方式, 來處理這種多重的相依性

2.. 解決方式

(1) 以下提供一個利用 CTE (Common Table Expression) 實作遞迴 (Recursive) 的方式, 可以把不論直接或間接相依於某個 table 的 view 會部找出來. 之後再逐一進行 refresh VIEW

-- ===========================
-- 建立自訂函式, 以取回相依於某的 Table 的所有 View
-- sys.sql_expression_dependencies 中的 referencing_xxx 為 child; referenced_xxx 為 parent
-- ===========================

CREATE FUNCTION [dbo].[ufn_GetDependencyByTable]
( @pi_tbname NVARCHAR(256) = N''
)
RETURNS @result  TABLE
( obj_id   INT
, obj_name  NVARCHAR(256) 
, obj_type  VARCHAR(2)
, obj_parent  NVARCHAR(256) 
)
AS
BEGIN
 DECLARE @ls_tbname NVARCHAR(256) = @pi_tbname;
 
 DECLARE @temp1  TABLE
 ( [obj_id]  INT
 , [obj_name]  NVARCHAR(256)
 , [obj_type]  VARCHAR(2)
 , [obj_parent] NVARCHAR(256) 
 )
 ;
 
 -- TABLE NAME 
 INSERT INTO @temp1
 SELECT  obj_id = A.object_id, obj_name = A.name, obj_type = 'U', obj_parent = ''
 FROM sys.objects A
 WHERE A.object_id = OBJECT_ID(@ls_tbname)
 ;
   
 WITH Views AS 
 ( 
 --initialization 
 SELECT obj_id = A.object_id, obj_name = A.name, obj_type = 'V', 
   obj_parent = ( SELECT X.name FROM sys.objects X
        WHERE X.object_id = B.referenced_id
       )
 FROM sys.objects A
 INNER JOIN sys.sql_expression_dependencies AS B
         ON ( A.object_id = B.referencing_id )
 WHERE A.type = 'V' 
 AND   B.referenced_id = OBJECT_ID(@ls_tbname)
 UNION  ALL
 --recursive execution 
 SELECT obj_id = A.object_id, obj_name = A.name, obj_type = 'V', 
   obj_parent = ( SELECT X.name FROM sys.objects X
        WHERE X.object_id = B.referenced_id
       )
 FROM sys.objects A
 INNER JOIN sys.sql_expression_dependencies AS B
         ON ( A.object_id = B.referencing_id )
 INNER JOIN Views V ON ( B.referenced_id  = V.obj_id )
 ) 
 INSERT INTO @temp1
 SELECT * FROM Views
 ;

 INSERT INTO @result
 ( obj_id
 , obj_name
 , obj_type
 , obj_parent
 ) SELECT * FROM @temp1;

 RETURN;

END
GO

-- ===========================
-- 執行 ufn_GetDependencyByTable
-- ===========================
SELECT * 
FROM [dbo].[ufn_GetDependencyByTable]('Products')
;
-- OUTPUT
-- obj_id  obj_name   obj_type obj_parent
-- 597577167 Products   U 
-- 645577338 vwProducts   V   Products
-- 885578193 vwProductsCookies V   vwProducts
-- 901578250 vwProductsCake  V   vwProducts

(2) 有沒有發現, 相依於 table:Products 的全部 VIEW, 都被列出來了 (3) 結合上述 REFRESH VIEW 的 script, 及 ufn_GetDependencyByTable() 自訂函式, 我們可以把所有相依於 table:Products 的 VIEW 一次作 refresh.

-- ===========================
-- REFRESH VIEW
-- ===========================
SET NOCOUNT ON;
DECLARE @ls_tbname NVARCHAR(256) = N'Products';
DECLARE @ls_vwname NVARCHAR(256) = N'';

CREATE TABLE #temp1
( [ViewName] NVARCHAR(256) 
);

INSERT INTO #temp1
SELECT A.obj_name
FROM   ufn_GetDependencyByTable('Products') A
WHERE  A.obj_type = 'V'
;

DECLARE cur_temp1 CURSOR
FOR 
SELECT *
FROM #temp1;

OPEN cur_temp1;

FETCH NEXT FROM cur_temp1 INTO @ls_vwname;

WHILE (@@FETCH_STATUS = 0)
BEGIN
 PRINT 'Refreshing --> ' + @ls_vwname;
 EXEC sp_refreshview  @ls_vwname;
 FETCH NEXT FROM cur_temp1 INTO @ls_vwname;
END;

CLOSE cur_temp1;
DEALLOCATE cur_temp1;
DROP TABLE #temp1;

-- OUTPUT
-- Refreshing --> vwProducts
-- Refreshing --> vwProductsCookies
-- Refreshing --> vwProductsCake

(4) 看來, 每個 View 都有作 refresh 了; 我們來檢視一下查詢的結果.

-- ===========================
-- 檢查一下
-- ===========================
SELECT *
FROM vwProducts
;
-- OUTPUT 
-- Id Name   Category Price ValidDay ShipMethod CategoryName
-- 1 夏威夷豆塔  1   50  14   1   餅乾類
-- 2 堅果塔   1   55  14   1   餅乾類
-- 3 養生堅果  1   60  14   1   餅乾類
-- 4 乳酪塔   1   60  14   1   餅乾類
-- 5 檸檬塔   1   60  14   1   餅乾類
-- 6 草莓塔   1   65  14   1   餅乾類
-- 7 輕乳酪蛋糕(片) 2   65  14   2   蛋糕類
-- 8 重乳酪蛋糕(片) 2   70  14   2   蛋糕類
-- 9 抹茶奶酪  3   90  14   3   奶酪類
-- 10 草莓奶酪  3   85  14   3   奶酪類
-- 11 芒果奶酪  3   85  14   3   奶酪類
-- 12 紅豆奶酪  3   80  14   3   奶酪類

SELECT *
FROM vwProductsCookies
;
-- OUTPUT
-- Id Name   Category Price ValidDay ShipMethod CategoryName
-- 1 夏威夷豆塔  1   50  14   1   餅乾類
-- 2 堅果塔   1   55  14   1   餅乾類
-- 3 養生堅果  1   60  14   1   餅乾類
-- 4 乳酪塔   1   60  14   1   餅乾類
-- 5 檸檬塔   1   60  14   1   餅乾類
-- 6 草莓塔   1   65  14   1   餅乾類

SELECT *
FROM vwProductsCake
;
-- 7 輕乳酪蛋糕(片) 2   65  14   2   蛋糕類
-- 8 重乳酪蛋糕(片) 2   70  14   2   蛋糕類

(5) 至此, 一切都正常了.

3.. 延伸閱讀 (2018.06.25 補充)

進一步思考, 如何取出全部 Table / View 相關的 View 呢? 可以想像一下, 應該可以由 sys.tables 去 CROSS APPLY ufn_GetDependencyByTable()
為了驗證結果, 先再加一個 View.

-- ===========================
-- 增加1個 View
-- ===========================
CREATE VIEW [dbo].[vwCategories]
AS
SELECT  C.*
FROM [Categories] C
;
GO

-- ===========================
-- 查出資料庫中所有 Table / View 的關係
-- ===========================
SELECT B.*
FROM sys.tables A
CROSS APPLY ufn_GetDependencyByTable(A.name) B
;
GO

-- OUTPUT:
-- obj_id  obj_name  obj_type obj_parent
-- 565577053 Categories  U 
-- 645577338 vwProducts  V   Categories
-- 1205579333 vwCategories V   Categories
-- 885578193 vwProductsCookies V  vwProducts
-- 901578250 vwProductsCake V   vwProducts
-- 597577167 Products  U 
-- 645577338 vwProducts  V   Products
-- 885578193 vwProductsCookies V  vwProducts
-- 901578250 vwProductsCake V   vwProducts

可以看到
level 0: table:Categories -->
level 1: view: vwProducts -->
level 1: view: vwCategories -->
level 2: view: vwProductsCookies / view: vwProductsCake
level 0: table:Products -->
level 1: view: vwProducts -->
level 2: view: vwProductsCookies / view: vwProductsCake

但如果要去除重複, 且保有原來關係的先後順序呢? 可以參考以下的 script.

-- ===========================
-- 查出資料庫中的 View (去除重複)
-- ===========================
-- 保留順序, 以便依先後順序, 保留最前面的 View
DECLARE @temp1  TABLE
 ( [obj_id]  INT
 , [obj_name]  NVARCHAR(256)
 , [obj_type]  VARCHAR(2)
 , [obj_parent] NVARCHAR(256) 
 , [seq]  int IDENTITY(1,1)
 )
 ;

INSERT INTO @temp1
( [obj_id]
, [obj_name]
, [obj_type]
, [obj_parent]
)
SELECT  B.*
FROM  sys.tables A
CROSS  APPLY ufn_GetDependencyByTable(A.name) B
;

-- SELECT *
-- FROM @temp1
-- ;
-- 
-- SELECT DISTINCT obj_name
-- FROM @temp1
-- ;

SELECT seq = MIN(seq), obj_name
FROM @temp1
WHERE obj_type = 'V'
GROUP BY obj_name
ORDER BY MIN(seq), obj_name
;

-- OUTPUT:
-- seq  obj_name
-- 2  vwProducts
-- 3  vwCategories
-- 4  vwProductsCookies
-- 5  vwProductsCake
--

4.. 結論

綜合上述, SQL Server 資料庫內的物件相依, 錯綜複雜, 要由其中找出相依性, 的確需要一些進階的處理方式.
以上僅供各位參考.

5.. 參考文件

(1) SQL Server Central, Rebuild or recompile all views, Doug Deneau, 2017/10/05

(2) SQL Server Portal, SQL SERVER – How to refresh all views related to modified tables, Muhammad Imran, 2014/06/15

(3) Microsoft Docs, sp_refreshview (Transact-SQL)

(4) Microsoft Docs, sys.sql_expression_dependencies (Transact-SQL)
(5) SQL Tips, Recursive Queries using Common Table Expressions (CTE) in SQL Server, Edwin Sarmiento, 2008/06/16

(6) 黑暗執行緒, 【茶包射手筆記】在 View 使用 SELECT * 的風險, 2017/02/15

(7) 傑士伯的IT學習之路, [SQL Server] Table新增欄位, 但對應的 View 卻沒有異動的處理方式 (初階) : 只取1層相依物件, 2018/06/14

傑士伯的IT學習之路

2018年6月16日星期六