IDENTITY設定用のDELETE時トリガ自動生成

毎回テンプレートから起こしているのが面倒になったので、一括生成用プロシージャを作ってみた。システム管理テーブルから、トリガ一覧の取得やテーブルの項目一覧取得などを使っているので、ちょちょいと変更していけば結構使いではある・・・かもｗ

--
--全てのテーブルにてIDENTITY値を調整するためのDELETEトリガを再生成する
--
ALTER PROCEDURE UP_CREATE_ALLDELETETRIGGER
AS
BEGIN

  --ワーク設定
  DECLARE @EXECUTESQL NVARCHAR(MAX);
  DECLARE @CRLF NVARCHAR(2);
  SET @CRLF = CHAR(13) + CHAR(10);

  --現在のトリガ一覧を取得
  DECLARE @OLDTRIGGERNAME NVARCHAR(MAX);
  DECLARE @TRIGGERCOUNT   INT;
  DECLARE CUR_TRG CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      ST.NAME,
      (SELECT COUNT(*) FROM SYS.TRIGGER_EVENTS STE2 WHERE STE2.OBJECT_ID = ST.OBJECT_ID) TRIGGERCOUNT
    FROM
      SYS.TRIGGERS ST
        INNER JOIN SYS.TRIGGER_EVENTS STE ON (ST.OBJECT_ID = STE.OBJECT_ID)
    WHERE
      ST.PARENT_CLASS = 1     AND  --対象の親    ：DML トリガ用のオブジェクトまたは列
      ST.TYPE         = 'TR'  AND  --対象の種類  ：トリガ（TR）
      STE.TYPE        = 3     AND  --イベント種類：DELETE時
      ST.NAME NOT LIKE '%#__FULLTEXT' ESCAPE '#' AND --フルテキスト用トリガの除外
      ST.NAME NOT LIKE '%#__SEARCH' ESCAPE '#'       --検索データ用トリガの除外
    GROUP BY
      ST.OBJECT_ID,
      ST.NAME   
    ORDER BY
      ST.NAME
    ;

  --既存トリガの削除
  OPEN CUR_TRG;
  FETCH NEXT FROM CUR_TRG INTO @OLDTRIGGERNAME, @TRIGGERCOUNT;

  WHILE (@@FETCH_STATUS = 0)
    BEGIN
      --DELETE時のみのトリガを削除
      IF @TRIGGERCOUNT = 1
        BEGIN
          SET @EXECUTESQL = 'DROP TRIGGER ' + @OLDTRIGGERNAME + '' + @CRLF;
          EXECUTE (@EXECUTESQL);
        END;

      FETCH NEXT FROM CUR_TRG INTO @OLDTRIGGERNAME, @TRIGGERCOUNT;
    END;

  CLOSE CUR_TRG;
  DEALLOCATE CUR_TRG;

  --テーブル一覧、IDENTITY列一覧を取得
  DECLARE @TABLENAME NVARCHAR(MAX);
  DECLARE @COLUMNNAME NVARCHAR(MAX);
  DECLARE CUR_IDENT CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      ST.NAME,
      SC.NAME
    FROM
      SYS.COLUMNS SC
       INNER JOIN SYS.TABLES ST ON (SC.OBJECT_ID = ST.OBJECT_ID)
    WHERE
      SC.IS_IDENTITY = 1
    ORDER BY
      ST.NAME, SC.NAME
    ;

  --新トリガの生成
  OPEN CUR_IDENT;
  FETCH NEXT FROM CUR_IDENT INTO @TABLENAME, @COLUMNNAME;

  WHILE (@@FETCH_STATUS = 0)
    BEGIN
      --トリガSQLを生成
      SET @EXECUTESQL = 'CREATE TRIGGER TRG_DEL_' + @TABLENAME + @CRLF
                      + '  ON ' + @TABLENAME + @CRLF
                      + '  AFTER DELETE ' + @CRLF
                      + 'AS ' + @CRLF
                      + 'BEGIN ' + @CRLF
                      + '  ' + @CRLF
                      + '  SET NOCOUNT ON;' + @CRLF
                      + '  ' + @CRLF
                      + '  DECLARE @COUNT INT;' + @CRLF
                      + '  SET @COUNT = 0;' + @CRLF
                      + '  SELECT @COUNT = COUNT(*) FROM DELETED;' + @CRLF
                      + '  IF @COUNT <= 0' + @CRLF
                      + '    RETURN;' + @CRLF
                      + '  ' + @CRLF
                      + '  DECLARE @MAXIDENT INT; ' + @CRLF
                      + '  SELECT @MAXIDENT = MAX(' + @COLUMNNAME + ') FROM ' + @TABLENAME + ' WITH (UPDLOCK);' + @CRLF
                      + '  IF @MAXIDENT IS NULL ' + @CRLF
                      + '    SET @MAXIDENT = 0;' + @CRLF
                      + '  DBCC CHECKIDENT (''' + @TABLENAME + ''', RESEED, @MAXIDENT) WITH NO_INFOMSGS; ' + @CRLF
                      + ' ' + @CRLF
                      + 'END; ' + @CRLF;
      EXECUTE (@EXECUTESQL);

     FETCH NEXT FROM CUR_IDENT INTO @TABLENAME, @COLUMNNAME;
    END;

  CLOSE CUR_IDENT;
  DEALLOCATE CUR_IDENT;

END;