Stored Procedure to Generate Drop and Create Index

sp_GenDropCreateIndexScript.


USE [Work]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GenDropCreateIndexScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GenDropCreateIndexScript]
GO


USE [Work]
GO
CREATE PROCEDURE [dbo].[sp_GenDropCreateIndexScript](
@SCHEMA_NAME VARCHAR(10),
    @TABLE_NAME VARCHAR(50),  
    @DROPSQL VARCHAR(max) OUTPUT,
    @CREATESQL VARCHAR(max) OUTPUT
)
AS


DECLARE
   @KEY_NAME SYSNAME,
   @INDEX_ID SMALLINT,   
   @TYPE SMALLINT,
   @TYPE_DESC VARCHAR(20),
   @UNIQUE VARCHAR(6),
   @DATA_SPACE SYSNAME,
   @STAT_NOCOMPUTE VARCHAR(3),
   @IGNORE_DUP_KEY VARCHAR(3), 
   @IS_PRIMARY_KEY SMALLINT,
   @IS_UNIQUE_CONSTRAINT SMALLINT,
   @FILL_FACTOR VARCHAR(20),
   @IS_PADDED VARCHAR(3),
   @IS_DISABLED SMALLINT,
   @ALLOW_ROW_LOCKS VARCHAR(3),
   @ALLOW_PAGE_LOCKS VARCHAR(3),
   
   @DROPCND VARCHAR(MAX),
   @CREATECND VARCHAR(MAX),
   @IDXCOLUMN_NAME SYSNAME,
   @ORDER VARCHAR(10),
   @INCLUDED SMALLINT,
   @IDXCOLUMNS SYSNAME,
   @INCCOLUMNS SYSNAME,
   
   @ON VARCHAR(2) = 'ON',
   @OFF VARCHAR(3) = 'OFF'


DECLARE CSR_IDXS CURSOR FOR
SELECT idx.name
 ,idx.type
 ,idx.index_id
      ,idx.type_desc
      ,CASE idx.is_unique
WHEN 0 THEN ''
WHEN 1 THEN 'UNIQUE'
  END
      ,ds.name
      ,CASE st.no_recompute
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,CASE idx.ignore_dup_key
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,idx.is_primary_key
      ,idx.is_unique_constraint
      ,CASE idx.fill_factor
WHEN 0 THEN ''
ELSE ', FILLFACTOR = ' + convert(VARCHAR,idx.fill_factor)
  END
      ,CASE idx.is_padded
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,idx.is_disabled
      ,CASE idx.allow_row_locks
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
      ,CASE idx.allow_page_locks
WHEN 0 THEN @OFF
WHEN 1 THEN @ON
  END
  FROM sys.indexes idx LEFT OUTER JOIN
sys.tables tbs ON idx.object_id = tbs.object_id AND idx.name IS NOT NULL LEFT OUTER JOIN
sys.data_spaces ds on idx.data_space_id = ds.data_space_id LEFT OUTER JOIN
sys.stats st on st.object_id = idx.object_id AND st.name = idx.name
  WHERE idx.is_primary_key = 0
AND tbs.name = @TABLE_NAME
AND schema_name(tbs.schema_id) = @SCHEMA_NAME
  ORDER BY idx.is_unique_constraint DESC


SET @DROPSQL = ''
SET @CREATESQL = ''


OPEN CSR_IDXS
       
WHILE 1 = 1   
BEGIN
   FETCH NEXT FROM CSR_IDXS   
   INTO @KEY_NAME   
   ,@TYPE
   ,@INDEX_ID
   ,@TYPE_DESC
   ,@UNIQUE
   ,@DATA_SPACE
   ,@STAT_NOCOMPUTE
   ,@IGNORE_DUP_KEY
   ,@IS_PRIMARY_KEY
   ,@IS_UNIQUE_CONSTRAINT
   ,@FILL_FACTOR
   ,@IS_PADDED
   ,@IS_DISABLED
   ,@ALLOW_ROW_LOCKS
   ,@ALLOW_PAGE_LOCKS
   
   IF @@FETCH_STATUS <> 0
BREAK

-- preparing drop statement 
   BEGIN
    SET @DROPCND = 'IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(''[' + @SCHEMA_NAME + '].[' + @TABLE_NAME +']'') AND name = ''' + @KEY_NAME + ''')' + CHAR(10) + 'BEGIN' +  CHAR(10)
SET @DROPSQL = @DROPSQL + @DROPCND

IF (@IS_UNIQUE_CONSTRAINT = 1)
       SET @DROPSQL = @DROPSQL + 'ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME   
       + '] DROP CONSTRAINT [' + @KEY_NAME + '];' + CHAR(10) + 'END;' + CHAR(10)
    ELSE
  SET @DROPSQL = @DROPSQL + 'DROP INDEX [' + @KEY_NAME +'] ON [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] WITH ( ONLINE = OFF );' + CHAR(10) + 'END;' + CHAR(10)
   END

-- preparing recreate statement 
   BEGIN
       DECLARE 
  @COUNTER INT,
  @INCLUDECNT INT,
  @TMP VARCHAR(MAX)
  
       -- get Foreign Key columns  
       DECLARE CSR_IDXCOLS CURSOR FOR 
       SELECT  COL_NAME(ic.object_id, ic.column_id) AS idx_col_name,
  CASE is_descending_key
WHEN 0 THEN 'ASC'
WHEN 1 THEN 'DESC'
  END,
  ic.is_included_column
       FROM    sys.indexes idx LEFT OUTER JOIN   
               sys.tables tbs ON tbs.OBJECT_ID = idx.object_id INNER JOIN   
               sys.index_columns ic ON ic.object_id = idx.object_id and ic.index_id = idx.index_id 
       WHERE   tbs.name = @TABLE_NAME 
               AND schema_name(tbs.schema_id) = @SCHEMA_NAME 
               AND ic.index_id = @INDEX_ID  
       ORDER BY ic.index_column_id, ic.key_ordinal 
       
       OPEN CSR_IDXCOLS  
       
       SET @COUNTER = 1
       SET @INCLUDECNT = 1
       SET @IDXCOLUMNS = ''
       SET @INCCOLUMNS = ''
         
       WHILE 1 = 1   
       BEGIN
  FETCH NEXT FROM  CSR_IDXCOLS INTO @IDXCOLUMN_NAME, @ORDER, @INCLUDED
  IF @@FETCH_STATUS <> 0
  BREAK
  
  IF (@INCLUDED = 0)
  BEGIN
  IF @COUNTER > 1    
  SET @IDXCOLUMNS = @IDXCOLUMNS + ', ' 
          
  SET @IDXCOLUMNS = @IDXCOLUMNS + '[' + @IDXCOLUMN_NAME + '] ' + @ORDER  
  SET @COUNTER = @COUNTER + 1
  END
  ELSE
  BEGIN
 IF @INCLUDECNT > 1    
  SET @INCCOLUMNS = @INCCOLUMNS + ', ' 
          
  SET @INCCOLUMNS = @INCCOLUMNS + '[' + @IDXCOLUMN_NAME + ']' 
  SET @INCLUDECNT = @INCLUDECNT + 1
  END


       END
       CLOSE CSR_IDXCOLS   
       DEALLOCATE CSR_IDXCOLS
       
       SET @CREATECND = 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(''[' + @SCHEMA_NAME + '].[' + @TABLE_NAME +']'') AND name = ''' + @KEY_NAME + ''')' + CHAR(10) + 'BEGIN' +  CHAR(10)
       SET @TMP = @CREATECND
       
       IF (@IS_UNIQUE_CONSTRAINT = 1)
   SET @TMP = @TMP + 'ALTER TABLE [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + '] ADD CONSTRAINT [' + @KEY_NAME + '] UNIQUE ' + @TYPE_DESC
       ELSE
SET @TMP = @TMP + 'CREATE ' + @UNIQUE + ' ' + @TYPE_DESC + ' INDEX [' + @KEY_NAME + '] ON [' + @SCHEMA_NAME + '].[' + @TABLE_NAME + ']'
       
       SET @TMP = @TMP + ' ( '+ @IDXCOLUMNS + ') '
       
       IF (LEN(@INCCOLUMNS)>0)
   SET @TMP = @TMP + 'INCLUDE (' + @INCCOLUMNS + ') '
   
       SET @TMP = @TMP + 'WITH ('
       + 'PAD_INDEX  = ' + @IS_PADDED
       + ', STATISTICS_NORECOMPUTE  = ' + @STAT_NOCOMPUTE
       + ', SORT_IN_TEMPDB = OFF'
       + ', IGNORE_DUP_KEY = ' + @IGNORE_DUP_KEY
       --+ ', DROP_EXISTING = OFF'
       + ', ONLINE = OFF'
       + ', ALLOW_ROW_LOCKS  = ' + @ALLOW_ROW_LOCKS
       + ', ALLOW_PAGE_LOCKS  = ' + @ALLOW_PAGE_LOCKS
       + @FILL_FACTOR
       + ') ON [' + @DATA_SPACE
       + '];' + CHAR(10)
       
       --statement to set index disabled
       IF (@IS_DISABLED = 1)
 SET @TMP = @TMP 
 + 'ALTER INDEX [' + @KEY_NAME
 + '] ON [' + @SCHEMA_NAME
 + '].[' + @TABLE_NAME
 + '] DISABLE;' + CHAR(10)
         
       SET @TMP = @TMP + 'END;' + CHAR(10)
       SET @CREATESQL = @CREATESQL + @TMP


    END


END
CLOSE CSR_IDXS   
DEALLOCATE CSR_IDXS
GO