はじめに
そもそも、Viewにアスタリスクを付けなければ良い話なのだが、プロジェクトによっては大量のアスタリスクで書きつくされたviewをメンテする事が許されず、苦しい思いをしているのではないだろうか。
今回はsp_refreshviewを依存関係順に流してくれるストアドを作成してみた。
検証用のデータ
まずは検証用のデータを作成する。
sample.sql
-- テーブルを作成
CREATE TABLE [dbo].[Table_1](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Table_1](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NOT NULL
) ON [PRIMARY]
-- 依存関係用のviewを作成
CREATE VIEW [dbo].[W_Table_1] AS
SELECT [ID]
,[Name]
FROM [dbo].[Table_1]
CREATE VIEW [dbo].[W_Table_2] AS
SELECT [ID]
,[Name]
FROM [dbo].[Table_2]
-- 一番上で参照されるview
CREATE VIEW [dbo].[V_Table] AS
SELECT [ID]
,[Name]
FROM [dbo].[W_Table_1]
UNION ALL
SELECT [ID]
,[Name]
FROM [dbo].[W_Table_2]
Viewの依存関係を調べる(その1)
依存関係はこんな形。
Viewの依存関係を調べる(その2)
MSDNのsp_refreshviewには変更されたオブジェクトに対する依存関係があるすべてのビューを更新するスクリプトの作成というサンプルがあり、これを先程のテーブルと合わせて実行してみる。
テーブルの場合はこれで問題ないが、viewで実行すると出てこなくなった。
よく見ると、テーブルに対すると明記されていたので思っていたのとは違う内容だった。
Viewの依存関係順にRefreshViewするストアドのサンプル
ネットで検索してみるとオブジェクトの依存関係を表示するカタログビューというのがあり、viewの依存関係順に作成されている素晴らしいサンプルがあったので作成したview全てを依存関係順にRefreshViewするストアドを作成した。
P_RefreshViewAll.sql
CREATE PROCEDURE dbo.P_RefreshViewAll
AS
BEGIN
DECLARE @ViewName NVARCHAR(128);
DECLARE @ExecSql NVARCHAR(150);
DECLARE ViewCursor CURSOR FOR SELECT Name FROM sysobjects WHERE xtype = 'V';
OPEN ViewCursor;
FETCH NEXT FROM ViewCursor INTO @ViewName
-- viewテーブル分ループ
WHILE (@@fetch_status = 0)
BEGIN
PRINT 'View:' + @ViewName
DECLARE @referencing_entity AS sysname = @ViewName;
DECLARE ExecCursor cursor FOR
WITH W_Depends
(
referenced_entity_name
, referenced_id
, Dependence_Order
)
AS
(
SELECT
MAIN_T.referenced_entity_name
, MAIN_T.referenced_id
, 0 AS DependenceOrder
FROM sys.sql_expression_dependencies AS MAIN_T
WHERE
OBJECT_NAME(MAIN_T.referencing_id) = @ViewName
UNION ALL
SELECT
sed.referenced_entity_name
, sed.referenced_id
, WiTH_T.Dependence_Order + 1 AS DependenceOrder
FROM W_Depends AS WiTH_T INNER JOIN sys.sql_expression_dependencies AS sed ON sed.referencing_id = WiTH_T.referenced_id
)
SELECT
T.ExecSql
FROM
(
SELECT
'EXEC sp_refreshview ''' + referenced_entity_name + '''' AS ExecSql
, Dependence_Order
, ROW_NUMBER() OVER (PARTITION BY referenced_entity_name ORDER BY referenced_entity_name , Dependence_Order DESC) AS RowNo
FROM W_Depends W INNER JOIN sysobjects S ON W.referenced_id = S.id
WHERE S.xtype = 'V'
) AS T
WHERE T.RowNo = 1
ORDER BY T.Dependence_Order DESC;
OPEN ExecCursor;
FETCH NEXT FROM ExecCursor INTO @ExecSql;
WHILE (@@fetch_status = 0)
BEGIN
PRINT @ExecSql;
EXEC (@ExecSql);
FETCH NEXT FROM ExecCursor INTO @ExecSql;
END
CLOSE ExecCursor;
DEALLOCATE ExecCursor;
FETCH NEXT FROM ViewCursor INTO @ViewName;
END
CLOSE ViewCursor;
DEALLOCATE ViewCursor;
END
GO