Colin の 資料庫小鋪

昨天, Colin接到一個好友的訊息, 詢問了一個有關資料表與View的問題, 描述大致如下 (Colin有做整理):

有一個Table, 假設叫tbl_A, 其中包含了二個欄位C1, C2.
又建了一個View, 使用SELECT * FROM tbl_A去取資料.
之後對tbl_A做了ALTER TABLE ... ADD COLUMN加入C3欄位, 可是View中沒有顯示C3.

當下, Colin回答了解決的方式. 之後Colin又把這個問題發給徒子徒孫們, 看看他們是否學習起來了, 沒想到...... 看來這個問題並不是大家喜歡去碰的, 因為回給我的幾乎都是 "重建呀~~~"....... 很微軟派的回答吶~~

基於這點, Colin就做了個簡單的sample給大家來玩玩看囉~~~

STEP 01: 首先, 先建立測試資料表和View.

/*****建立測試資料表*****/
USE tempdb;
GO

CREATE TABLE tbl_A (C1 int, C2 int);
GO

INSERT INTO tbl_A VALUES (1,1);
GO

CREATE VIEW vw_A AS SELECT * FROM tbl_A;
GO

SELECT * FROM tbl_A;
SELECT * FROM vw_A;
GO

回傳的結果如下所示:

C1          C2
----------- -----------
1           1

(1 個資料列受到影響)

C1          C2
----------- -----------
1           1

(1 個資料列受到影響)

STEP 02: 進行資料表加入欄位的動作, 重現問題.

/*****重現問題*****/
ALTER TABLE tbl_A ADD C3 int;
GO

SELECT * FROM tbl_A;
SELECT * FROM vw_A;
GO

回傳的結果如下所示, 也確實, C3並沒有在View中被顯示出來:

C1          C2          C3
----------- ----------- -----------
1           1           NULL

(1 個資料列受到影響)

C1          C2
----------- -----------
1           1

(1 個資料列受到影響)

STEP 03: 要解決這個問題, 就要透過sp_refershview這個store procedure. 相關描述可以參考下列文件:
sp_refreshview (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms187821.aspx

/*****使用sp_refershview <view_name>*****/
EXEC sp_refreshview 'vw_A';
GO

SELECT * FROM tbl_A;
SELECT * FROM vw_A;
GO

回傳的結果如下所示, 進行sp_refershview後, 即可在view中正確的顯示.

C1          C2          C3
----------- ----------- -----------
1           1           NULL

(1 個資料列受到影響)

C1          C2          C3
----------- ----------- -----------
1           1           NULL

(1 個資料列受到影響)

以上, 看似奇怪的問題已經被解決了. 多半這時...... Colin以站在User的角度來思考的話, 就會提問了 "如果改了某個Table的schema, 該如何一次性的進行有關連的View更新呢?"

沒錯, 很多人回答 "Cursor", 這是一個不錯的idea, 至少不用一支一支的打開來看. 不過, Colin肯定會說 "你一定沒有仔細的看文件", 因為文件裡真的有教~~ 哈哈哈~~ 以下就是文件中使用AdventureWorks2012進行的Demo Code.

DEMO: Assume that the table Person.Person was changed in a way that would affect the definition of any views that are created on it. The following example creates a script that refreshes the metadata for all views that have a dependency on table Person.Person.

USE AdventureWorks2012;
GO

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed
    ON so.object_id = sed.referencing_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('Person.Person');

以上, 分享給大家囉~~

註: 移除先前測試資料的相關程式碼:

/*****移除測試資料*****/
USE tempdb;
GO

DROP VIEW vw_A;
DROP TABLE tbl_A;