MS-SQL 인덱스 재생성 프로시져

USE [Database명]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[MYDB_REINDEX]

AS

BEGIN   

   Declare @vCount Int;

   select a.name INAME, b.name TNAME

   INTO #TEMPIDX

   from ( select distinct id, name from sys.sysindexes where name like 'PK_%' or name like 'IX_%' ) a 

   left outer join sys.objects b on (a.id = b.object_id);

   SET @vCount = @@ROWCOUNT;

   

   Declare @vIdxName varchar(200), @vTabName varchar(200), @vSQL varchar(500), @vSEQ int ;

   Declare Cur_Tr02 CURSOR FOR

      SELECT INAME, TNAME FROM #TEMPIDX;

   SET @vSEQ = 0;

   OPEN Cur_Tr02

   FETCH NEXT FROM Cur_Tr02 

              INTO @vIdxName, @vTabName

         WHILE(@@FETCH_STATUS = 0)

   BEGIN

      SET @vSQL = 'ALTER INDEX ' + @vIdxName + ' ON ' + @vTabName + ' REBUILD';

      SET @vSEQ = @vSEQ + 1;

      Exec ( @vSQL ); 

      PRINT LTRIM(STR(@vSEQ)) + ' / ' + LTRIM(STR(@vCount)) + ' : ' + @vIdxName + '(' + @vTabName + ')';

      FETCH NEXT FROM Cur_Tr02 

             INTO @vIdxName, @vTabName;

   END;   

   CLOSE Cur_Tr02;

   DEALLOCATE Cur_Tr02; 

    

   PRINT '----- 끝 -----';

END