index_cleanup 인덱스 지우기


IF EXISTS (SELECT name FROM sysobjects WHERE id = OBJECT_ID('index_cleanup')
                    AND OBJECTPROPERTY(OBJECT_ID('index_cleanup'), 'IsProcedure')=1)

    DROP PROCEDURE index_cleanup
GO

CREATE PROCEDURE index_cleanup
    @tabname nvarchar(150)
AS

DECLARE @idx_name NVARCHAR(150)
DECLARE @drop_idx_string NVARCHAR(200)

SET NOCOUNT ON

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
                              WHERE table_type = 'base table' AND table_name = @tablename)
    BEGIN
        RAISERROR('The table : ''%s'' does not exist.', 16, 1, @tabname)
        RETURN (1)
    END

SET @tablename = OBJECT_ID(@tabname)
IF EXISTS (SELECT id FROM sysindexes
                 WHERE id = @tabname AND indid BETWEEN 1 AND 254
                     AND status IN (96,10485856,8388704))

BEGIN
    DECLARE idx_cursor CURSOR
        FOR SELECT name FROM sysindexes
        WHERE id = @tabname AND indid BETWEEN 1 AND 254
            AND status IN (96,10485856,8388704)

    OPEN idx_cursor
    FETCH NEXT FROM idx_cursor INTO @idx_name
    WHIL @@FETCH_STATUS = 0
    BEGIN
        SET @drop_idx_string = ('DROP STATISTICS' + OBJECT_NAME(@tabname) + '.' + @idx_name)
        EXECUTE(@drop_idx_string)
        FETCH NEXT FROM idx_cursor INTO @id_name
    END
CLOSE idx_cursor
DEALLOCATE idx_cursor
END
PRINT '    ***Statistics Deleted ***'

IF EXISTS (SEECT id FROM sysindexes
                 WHERE id = @tabname AND indid BETWEEN 1 AND 254
         AND status NOT IN (96,10485856,8388704))
BEGIN
   DECLARE idx_cursor CURSOR
      FOR SELECT name FROM sysindexes
   WHERE id=@tabname AND indid BETWEEN 1 AND 254
       AND status NOT IN (96,10485856,8388704)
   OPEN idx_cursor
   FETCH NEXT FROM idx_cursor INTO @idx_name
     WHILE @@FETCH_STATUS = 0
 BEGIN
    SET @drop_idx_string = ('DROP INDEX '+OBJECT_NAME(@tabname)+'.'+@idx_name)
    EXECUTE(@drop_idx_string)
    FETCH NEXT FROM idx_cursor INTO @idx_name
 END
   CLOSE idx_cursor
   DEALLOCATE idx_cursor
END
PRINT '     *** Indexes Deleted ***'

GO

END