1
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

posted at

updated at

SQLServerのviewで依存関係順にsp_refreshviewしたい

はじめに

そもそも、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)

依存関係はこんな形。

main.png

Viewの依存関係を調べる(その2)

MSDNのsp_refreshviewには変更されたオブジェクトに対する依存関係があるすべてのビューを更新するスクリプトの作成というサンプルがあり、これを先程のテーブルと合わせて実行してみる。

sample.png

テーブルの場合はこれで問題ないが、viewで実行すると出てこなくなった。

sample.png

よく見ると、テーブルに対すると明記されていたので思っていたのとは違う内容だった。

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

参考

  1. sp_refreshview
  2. オブジェクトの依存関係を表示するカタログビュー
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
1
Help us understand the problem. What are the problem?