Generate Comands to Create/Drop all Indexes in a Database

DROP PROCEDURE dbo.usp_GenerateCreateDropIndexCommands

GO

CREATE PROCEDURE dbo.usp_GenerateCreateDropIndexCommands(@TableName SYSNAME = NULL)

AS

/***************************************************************

Purpose: To generate commands to create or drop all Indexes

No changes are made by this storted procedure

Author: ChillyDBA

History: 7 Jul 2012

****************************************************************/

CREATE TABLE #Indexes

(

DatabaseName SYSNAME NULL,

TableSchema SYSNAME NULL,

TableName SYSNAME NULL,

IndexName SYSNAME NULL,

IndexType VARCHAR(20),

IndexColumns_SO VARCHAR(1000),

IncludedColumns_SO VARCHAR(1000),

FileGroupName SYSNAME NULL,

Fill_Factor INT,

IsPadded INT,

NoRecompute INT,

AllowRowLocks INT,

AllowPageLocks INT

)

CREATE TABLE #DropCommands

(

CommandID INT IDENTITY(1,1),

Command VARCHAR(8000)

)

CREATE TABLE #CreateCommands

(

CommandID INT IDENTITY(1,1),

Command VARCHAR(8000)

)

DECLARE

@Command VARCHAR(8000),

@IndexName SYSNAME

-- retrieve all Index information for the database

INSERT #Indexes

(

DatabaseName,

TableSchema,

TableName,

IndexName,

IndexType,

IndexColumns_SO,

IncludedColumns_SO,

FileGroupName,

Fill_Factor,

IsPadded,

NoRecompute,

AllowRowLocks,

AllowPageLocks

)

SELECT

DatabaseName,

TableSchema,

TableName,

IndexName,

IndexType,

IndexColumns_SO,

IncludedColumns_SO,

FileGroupName,

Fill_Factor,

IsPadded,

NoRecompute,

AllowRowLocks,

AllowPageLocks

FROM dbo.udf_GetIndexColumns(@TableName) fkc

-- loop through all Index details to create the commands

SELECT @IndexName = MIN(IndexName)

FROM #Indexes

WHILE @IndexName IS NOT NULL

BEGIN

SELECT

@Command = ''

-- create the DROP Index command

SELECT @Command = @Command

+ 'DROP INDEX ['

+ TableSchema

+ '].['

+ TableName

+ '].['

+ IndexName

+ ']'

FROM #Indexes

WHERE IndexName = @IndexName

INSERT #DropCommands(Command)

VALUES (@Command)

-- good practice to zero the variable as is the results of next query are NULL,

-- then the old value (drop command) will remain in the variable

SELECT @Command = ''

SELECT @Command = @Command

+ 'CREATE '

+ IndexType

+ ' INDEX ['

+ IndexName

+ '] ON ['

+ TableSchema

+ '].['

+ TableName

+ '] ('

+ IndexColumns_SO

+ ') '

+ CASE

WHEN IncludedColumns_SO IS NOT NULL THEN

' INCLUDE ('

+ IncludedColumns_SO

+ ')'

ELSE ''

END

+ ' WITH (PAD_INDEX =' + CASE IsPadded WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ', STATISTICS_NORECOMPUTE =' + CASE NoRecompute WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ', SORT_IN_TEMPDB =OFF' -- not stored in sys.indexes so use default value

+ ', IGNORE_DUP_KEY =OFF' -- not stored in sys.indexes so use default value

+ ', ONLINE =OFF' -- not stored in sys.indexes so use default value

+ ', ALLOW_ROW_LOCKS =' + CASE AllowRowLocks WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ', ALLOW_PAGE_LOCKS =' + CASE AllowPageLocks WHEN 0 THEN 'OFF' ELSE 'ON' END

+ ') ON ['

+ FileGroupName

+ ']'

FROM #Indexes

WHERE IndexName = @IndexName

INSERT #CreateCommands(Command)

VALUES (@Command)

SELECT @IndexName = MIN(IndexName)

FROM #Indexes

WHERE IndexName > @IndexName

END

SELECT Command AS 'Commands to DROP Indexes'

FROM #DropCommands

ORDER BY CommandID

SELECT Command AS 'Commands to CREATE Indexes'

FROM #CreateCommands

ORDER BY CommandID

DROP TABLE #DropCommands

DROP TABLE #CreateCommands

DROP TABLE #Indexes