はじめに

そもそも、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の依存関係を調べる（その１）

依存関係はこんな形。

Viewの依存関係を調べる（その２）

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

参考