[SQL Server] Table新增欄位, 但對應的 View 卻沒有異動的處理方式 (sp_refreshview) (初階) : 只取1層相依物件

0.. 前言

最近發現某個 Table 加上欄位後, 但相依於該 Table 的 View 卻沒有異動的狀況.
本文只取1層的相依物件, 若要取全部相依物件, 請參考另一篇進階的文章
茲分為以下幾個部份及實作步驟進行說明:

1.. 問題重現
2.. 解決方式
3.. 結論
4.. 參考文件

1.. 問題重現

(1) 原始狀態

-- ===========================
-- 建立資料庫
-- ===========================

DROP DATABASE IF EXISTS Cookies;
GO
CREATE DATABASE Cookies;
GO

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

-- ===========================
-- 移除 View / Table
-- ===========================
DROP VIEW  IF EXISTS [dbo].[vwProducts];
GO
DROP TABLE IF EXISTS [dbo].[Products];
GO
DROP TABLE IF EXISTS [dbo].[Categories]
GO

-- ===========================
-- 建立 Table / View
-- ===========================
CREATE TABLE [dbo].[Categories]
( [Id]              INT             NOT NULL
, [Name]            NVARCHAR(30)   NOT NULL
CONSTRAINT PK_Catgories PRIMARY KEY ( [Id] )
);
GO
CREATE TABLE [dbo].[Products]
( [Id]    INT            NOT NULL
, [Name]            NVARCHAR(30)   NOT NULL
, [Category]    INT             NOT NULL
, [Price]           INT             NOT NULL
  CONSTRAINT PK_Products PRIMARY KEY ( [Id] )
, CONSTRAINT FK_Categories FOREIGN KEY ( [Category] )
                           REFERENCES [dbo].[Categories] ( [Id] )
);
GO
CREATE VIEW [dbo].[vwProducts]
AS
SELECT  P.*, CategoryName = C.[Name]
FROM [Products] P
LEFT JOIN [Categories] C ON (P.[Category] = C.[Id])
;
GO

-- ===========================
-- 新增測試資料
-- ===========================
INSERT INTO [dbo].[Categories]
VALUES ( 1, N'餅乾類' )
,      ( 2, N'蛋糕類' )
,      ( 3, N'奶酪類' )
;
GO
INSERT INTO [dbo].[Products]
VALUES ( 1, N'夏威夷豆塔', 1, 50 )
,      ( 2, N'堅果塔', 1, 55 )
,      ( 3, N'養生堅果', 1, 60 )
,      ( 4, N'乳酪塔', 1, 60 )
,      ( 5, N'檸檬塔', 1, 60 )
,      ( 6, N'草莓塔', 1, 65 )
,      ( 7, N'輕乳酪蛋糕(片)', 2, 65 )
,      ( 8, N'重乳酪蛋糕(片)', 2, 70 )
,      ( 9, N'抹茶奶酪', 3, 90 )
,      ( 10, N'草莓奶酪', 3, 85 )
,      ( 11, N'芒果奶酪', 3, 85 )
,      ( 12, N'紅豆奶酪', 3, 80 )
;
GO

-- ===========================
-- 查詢資料
-- ===========================
SELECT *
FROM   [dbo].[vwProducts]
;
GO

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

(2) 新增欄位

-- ===========================
-- 在 TABLE: Products 加上欄位 
-- ===========================
ALTER TABLE [dbo].[Products]
ADD [ValidDay]  INT  NULL
;
GO

UPDATE [dbo].[Products]
SET [ValidDay] = 14
;
GO

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

(3) 查詢後, 發現怪怪的狀況, 有沒有發現, CategoryName 的值, 居然是我們剛剛加入的 [ValidDay]

-- ===========================
-- 再次查詢資料 (怪怪的 ...)
-- ===========================
SELECT *
FROM   [dbo].[vwProducts]
;
GO

-- OUTPUT
-- Id Name   Category Price 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 篇的作法, 並改用 CURSOR 的方式, 應該比較容易理解. 一般而言, 通常會有以下 2 種情境.

(1) 剛改完 Table, 還記得改過的 Table 名稱.

(2) 改了好幾個 Table, 經過一段時間, 已經忘了有那些 Table 有異動過, 但依稀記大概什麼時候開始異動; 或者乾脆全部 Table 都找出來.

以下茲就上述 2 種情境, 列出解決方式, 其實主要重點在於 sp_refreshview 這個 Stored Procedure, 及 sql_expression_dependencies 這個 View; 細節可以參考 Microsoft Docs, 這裡就不多作描述.

(1) 剛改完 Table, 還記得改過的 Table 名稱.

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 DISTINCT A.name
FROM sys.objects A
INNER JOIN sys.sql_expression_dependencies AS B
        ON ( A.object_id = B.referencing_id )
WHERE A.type = 'V' 
AND   A.name LIKE 'vw%'  -- 額外的篩選條件
AND   B.referenced_id = OBJECT_ID(@ls_tbname)
;

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;

(2) 改了好幾個 Table, 經過一段時間, 已經忘了有那些 Table 有異動過, 但依稀記大概什麼時候開始異動; 或者乾脆全部 Table 都找出來.

SET NOCOUNT ON;
DECLARE @ld_modify  DATETIME =  '2018-6-14';
DECLARE @ls_tbname NVARCHAR(256) = N'';
DECLARE @ls_vwname NVARCHAR(256) = N'';

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

INSERT INTO #temp1
SELECT DISTINCT C.name, A.name
FROM sys.objects A
INNER JOIN sys.sql_expression_dependencies AS B ON ( A.object_id = B.referencing_id )
INNER JOIN sys.tables AS C ON (B.referenced_id = C.object_id)
WHERE A.type = 'V' 
AND   A.name LIKE 'vw%'  -- 額外的篩選條件
AND   C.modify_date >= @ld_modify
;

DECLARE cur_temp1 CURSOR
FOR 
SELECT *
FROM #temp1;

OPEN cur_temp1;

FETCH NEXT FROM cur_temp1 INTO @ls_tbname, @ls_vwname;

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

CLOSE cur_temp1;
DEALLOCATE cur_temp1;
DROP TABLE #temp1;

重新查詢, 可得正確的結果

-- ===========================
-- 再次查詢資料 (正常了)
-- ===========================
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   奶酪類

3.. 結論

如果您的應用系統有用到 View 的話, 當您在增刪 Table 欄位時, 若不確定相依於該 Table 的 View 定義是否含有 SELECT *, 最好重新整理(refresh) View, 以避免發生本文所描述的問題.
或許您會想到, View 的定義裡, 不要用 SELECT * 的方式, 乖乖一個欄位一個欄位 SELECT, 也可以避開該問題; 但這樣 base Table 增刪欄位時, 如果 View 有用到的話, 也要跟著一併增刪欄位, 這就不是 sp_refreshview 所能處理, 而是要手工查 sql_expression_dependencies, 找出相關的 View, 逐一修改.

4.. 參考文件

(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) 黑暗執行緒, 【茶包射手筆記】在 View 使用 SELECT * 的風險, 2017/02/15

傑士伯的IT學習之路

2018年6月14日星期四