Learning Code & Activity: September 2015


How to use :

First execute both scripts (on SSMS) on your database and save the result. It will generate drop index  and create index for all tables on database.

After doing this, please execute drop indexes result and then execute create index result.

Please verify the result.

--First Script----------

---------Drop indexes start

DECLARE @SchemaName VARCHAR(256)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR
FOR
SELECT schema_name(t.schema_id)
 , t.NAME
 , i.NAME
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.type > 0
 AND t.is_ms_shipped = 0
 AND t.NAME <> 'sysdiagrams'
 AND (
  is_primary_key = 0
  AND is_unique_constraint = 0
  )

OPEN CursorIndexes

FETCH NEXT
FROM CursorIndexes
INTO @SchemaName
 , @TableName
 , @IndexName

WHILE @@fetch_status = 0
BEGIN
 SET @TSQLDropIndex = 'DROP INDEX ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@IndexName)

 PRINT @TSQLDropIndex

 FETCH NEXT
 FROM CursorIndexes
 INTO @SchemaName
  , @TableName
  , @IndexName
END

CLOSE CursorIndexes

DEALLOCATE CursorIndexes
--------------------Drop indexes end--------------

PRINT '  '
PRINT '-------- DROP AND CREATE INDEXES BY VIMAL------------------'
PRINT '  '
GO

--Second Script----------

--------------------Create indexes start-----------
DECLARE @SchemaName VARCHAR(100)
DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @ColumnName VARCHAR(100)
DECLARE @is_unique VARCHAR(100)
DECLARE @IndexTypeDesc VARCHAR(100)
DECLARE @FileGroupName VARCHAR(100)
DECLARE @is_disabled VARCHAR(100)
DECLARE @IndexOptions VARCHAR(max)
DECLARE @IndexColumnId INT
DECLARE @IsDescendingKey INT
DECLARE @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex VARCHAR(max)
DECLARE @TSQLScripDisableIndex VARCHAR(max)

DECLARE CursorIndex CURSOR
FOR
SELECT schema_name(t.schema_id) [schema_name]
 , t.NAME
 , ix.NAME
 , CASE 
  WHEN ix.is_unique = 1
   THEN 'UNIQUE '
  ELSE ''
  END
 , ix.type_desc
 , CASE 
  WHEN ix.is_padded = 1
   THEN 'PAD_INDEX = ON, '
  ELSE 'PAD_INDEX = OFF, '
  END + CASE 
  WHEN ix.allow_page_locks = 1
   THEN 'ALLOW_PAGE_LOCKS = ON, '
  ELSE 'ALLOW_PAGE_LOCKS = OFF, '
  END + CASE 
  WHEN ix.allow_row_locks = 1
   THEN 'ALLOW_ROW_LOCKS = ON, '
  ELSE 'ALLOW_ROW_LOCKS = OFF, '
  END + CASE 
  WHEN INDEXPROPERTY(t.object_id, ix.NAME, 'IsStatistics') = 1
   THEN 'STATISTICS_NORECOMPUTE = ON, '
  ELSE 'STATISTICS_NORECOMPUTE = OFF, '
  END + CASE 
  WHEN ix.ignore_dup_key = 1
   THEN 'IGNORE_DUP_KEY = ON, '
  ELSE 'IGNORE_DUP_KEY = OFF, '
  END + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CASE 
  WHEN ix.fill_factor = 0
   THEN '100'
  ELSE CAST(ix.fill_factor AS VARCHAR(3))
  END AS IndexOptions
 , ix.is_disabled
 , FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
INNER JOIN sys.indexes ix ON t.object_id = ix.object_id
WHERE ix.type > 0
 AND ix.is_primary_key = 0
 AND ix.is_unique_constraint = 0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 AND t.is_ms_shipped = 0
 AND t.NAME <> 'sysdiagrams'
ORDER BY schema_name(t.schema_id)
 , t.NAME
 , ix.NAME

OPEN CursorIndex

FETCH NEXT
FROM CursorIndex
INTO @SchemaName
 , @TableName
 , @IndexName
 , @is_unique
 , @IndexTypeDesc
 , @IndexOptions
 , @is_disabled
 , @FileGroupName

WHILE (@@fetch_status = 0)
BEGIN
 DECLARE @IndexColumns VARCHAR(max)
 DECLARE @IncludedColumns VARCHAR(max)

 SET @IndexColumns = ''
 SET @IncludedColumns = ''

 DECLARE CursorIndexColumn CURSOR
 FOR
 SELECT col.NAME
  , ixc.is_descending_key
  , ixc.is_included_column
 FROM sys.tables tb
 INNER JOIN sys.indexes ix ON tb.object_id = ix.object_id
 INNER JOIN sys.index_columns ixc ON ix.object_id = ixc.object_id
  AND ix.index_id = ixc.index_id
 INNER JOIN sys.columns col ON ixc.object_id = col.object_id
  AND ixc.column_id = col.column_id
 WHERE ix.type > 0
  AND (
   ix.is_primary_key = 0
   OR ix.is_unique_constraint = 0
   )
  AND schema_name(tb.schema_id) = @SchemaName
  AND tb.NAME = @TableName
  AND ix.NAME = @IndexName
 ORDER BY ixc.index_column_id

 OPEN CursorIndexColumn

 FETCH NEXT
 FROM CursorIndexColumn
 INTO @ColumnName
  , @IsDescendingKey
  , @IsIncludedColumn

 WHILE (@@fetch_status = 0)
 BEGIN
  IF @IsIncludedColumn = 0
   SET @IndexColumns = @IndexColumns + QUOTENAME(@ColumnName) + CASE 
     WHEN @IsDescendingKey = 1
      THEN ' DESC, '
     ELSE ' ASC, '
     END
  ELSE
   SET @IncludedColumns = @IncludedColumns + QUOTENAME(@ColumnName) + ', '

  FETCH NEXT
  FROM CursorIndexColumn
  INTO @ColumnName
   , @IsDescendingKey
   , @IsIncludedColumn
 END

 CLOSE CursorIndexColumn

 DEALLOCATE CursorIndexColumn

 SET @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns) - 1)
 SET @IncludedColumns = CASE 
   WHEN len(@IncludedColumns) > 0
    THEN substring(@IncludedColumns, 1, len(@IncludedColumns) - 1)
   ELSE ''
   END
 --print @IndexColumns
 --print @IncludedColumns
 SET @TSQLScripCreationIndex = ''
 SET @TSQLScripDisableIndex = ''
 SET @TSQLScripCreationIndex = 'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '(' + @IndexColumns + ') ' + CASE 
   WHEN len(@IncludedColumns) > 0
    THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns + ')'
   ELSE ''
   END + CHAR(13) + 'WITH (' + @IndexOptions + ') ON ' + QUOTENAME(@FileGroupName) + ';'

 IF @is_disabled = 1
  SET @TSQLScripDisableIndex = CHAR(13) + 'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)

 PRINT @TSQLScripCreationIndex
 PRINT @TSQLScripDisableIndex

 FETCH NEXT
 FROM CursorIndex
 INTO @SchemaName
  , @TableName
  , @IndexName
  , @is_unique
  , @IndexTypeDesc
  , @IndexOptions
  , @is_disabled
  , @FileGroupName
END

CLOSE CursorIndex

DEALLOCATE CursorIndex
 ----------------------Create indexes end--------

Drop And Recreate all indexes on all tables of a database :