フルテキスト検索設定用のストアドプロシージャ

前回同様面倒くさくなったのでストアド化させたｗ
カタログの削除・生成とインデックスの削除・生成用のサンプルとして。

/*
特定のテーブルにおけるフルテキストインデックス、フルテキストカタログを再作成する
*/
ALTER PROCEDURE CREATE_FULLTEXTCATALOG(@tableName NVARCHAR(MAX))
AS
BEGIN

  DECLARE @CRLF NVARCHAR(2);
  SET @CRLF = CHAR(13) + CHAR(10);

  DECLARE @INDEXCOUNT     INT;
  DECLARE @DBNAME         NVARCHAR(MAX);
  DECLARE @CATALOGNAME    NVARCHAR(MAX);
  DECLARE @COLUMNTYPE     INT;
  DECLARE @COLUMNNAME     NVARCHAR(MAX);
  DECLARE @INDEXNAME      NVARCHAR(MAX);
  DECLARE @PRIMARYKEYNAME NVARCHAR(MAX);
  DECLARE @TARGETCOLUMN   NVARCHAR(MAX);
  DECLARE @EXECUTESQL     NVARCHAR(MAX);

  --実行データベース名を取得
  SET @DBNAME = DB_NAME();

  --フルテキストカタログ取得
  DECLARE CUR_CATALOG CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      SFC.NAME CATALOGNAME
    FROM
      SYS.FULLTEXT_CATALOGS SFC
        INNER JOIN SYS.FULLTEXT_INDEXES SFI ON (SFC.FULLTEXT_CATALOG_ID = SFI.FULLTEXT_CATALOG_ID)
          INNER JOIN  SYS.TABLES STS ON (SFI.OBJECT_ID = STS.OBJECT_ID)
    WHERE
      STS.NAME = @tableName;

  --対象テーブルのフルテキスト対象項目取得
  DECLARE CUR_TABLE CURSOR LOCAL FORWARD_ONLY FOR
    SELECT
      SC.SYSTEM_TYPE_ID,
      SC.NAME
    FROM
      SYS.COLUMNS SC
        INNER JOIN SYS.TABLES STS ON (SC.OBJECT_ID = STS.OBJECT_ID)
    WHERE
      STS.NAME      = @tableName AND
      SC.MAX_LENGTH = -1;

  --対象テーブルのプライマリキー取得
  SET @PRIMARYKEYNAME = '';
  SELECT
    @PRIMARYKEYNAME = SI.NAME
  FROM
    SYS.TABLES ST
      INNER JOIN SYS.INDEXES SI ON (ST.OBJECT_ID = SI.OBJECT_ID) 
  WHERE
    ST.NAME = @tableName AND
    SI.TYPE = 1;

  --既存フルテキストインデックスの削除
    SELECT
      @INDEXCOUNT = COUNT(*)
    FROM
      SYS.FULLTEXT_INDEXES SFI
        INNER JOIN SYS.TABLES STS ON (SFI.OBJECT_ID = STS.OBJECT_ID)
    WHERE
      STS.NAME = @tableName;

    IF @INDEXCOUNT > 0
      BEGIN
        SET @EXECUTESQL = 'DROP FULLTEXT INDEX ON ' + @tableName;
        EXECUTE (@EXECUTESQL);
        PRINT @EXECUTESQL + @CRLF;
      END;

  --既存のフルテキストカタログの削除
  OPEN CUR_CATALOG;
  FETCH NEXT FROM CUR_CATALOG INTO @CATALOGNAME;
  WHILE (@@FETCH_STATUS = 0)
    BEGIN
      SET @EXECUTESQL = 'DROP FULLTEXT CATALOG ' + @CATALOGNAME;
      EXECUTE (@EXECUTESQL);
      PRINT @EXECUTESQL + @CRLF;

      FETCH NEXT FROM CUR_CATALOG INTO @CATALOGNAME;
    END;
  CLOSE CUR_CATALOG;
  DEALLOCATE CUR_CATALOG;

  --フルテキストカタログの新規作成
  SET @EXECUTESQL = 'CREATE FULLTEXT CATALOG FTC_' + @DBNAME + '_' + @tableName;
  EXECUTE (@EXECUTESQL);
  PRINT @EXECUTESQL + @CRLF;

  --フルテキストインデックスの新規作成
  SET @TARGETCOLUMN = '';
  OPEN CUR_TABLE;
  FETCH NEXT FROM CUR_TABLE INTO @COLUMNTYPE, @COLUMNNAME;
  WHILE (@@FETCH_STATUS = 0)
    BEGIN
      IF @TARGETCOLUMN <> ''
        SET @TARGETCOLUMN = @TARGETCOLUMN + ', ';
      SET @TARGETCOLUMN = @TARGETCOLUMN + @COLUMNNAME + ' ';
      IF @COLUMNTYPE <> 231
        SET @TARGETCOLUMN = @TARGETCOLUMN + 'TYPE COLUMN EXTENSION ';

      SET @TARGETCOLUMN = @TARGETCOLUMN + ' LANGUAGE Japanese';

      FETCH NEXT FROM CUR_TABLE INTO @COLUMNTYPE, @COLUMNNAME;
    END;
  CLOSE CUR_TABLE;
  DEALLOCATE CUR_TABLE;
  SET @EXECUTESQL = 'CREATE FULLTEXT INDEX ON ' + @tableName + '(' + @TARGETCOLUMN + ')' + @CRLF
                  + ' KEY INDEX ' + @PRIMARYKEYNAME + @CRLF
                  + ' ON FTC_' + @DBNAME + '_' + @tableName + @CRLF
                  + ' WITH CHANGE_TRACKING AUTO' + @CRLF;
  EXECUTE (@EXECUTESQL);
  PRINT @EXECUTESQL + @CRLF;

  PRINT 'フルテキストカタログ、インデックスの生成を行いました。ログのダンプを行った後に有効となります。';

END;