Generate Comands to Create/Drop all Indexes in a Database

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: ChillyDBA

Date: 7 Jul 2012

Description

This SP makes use of the function to get index details.

Returns 2 lists of commands:

    1. Create all Indexes in a database using the definitions already in place in the database

    2. Drop all Indexes currently in place in the database

NOTE: No changes are made to database structure when running this SP - it only generates TSQL.

NOTE2: The index options in the 'WITH' command in the CREATE scripts were a little challenging to track down in the system tables. In these cases, I have set them to default values as specified in SQL BOL. At a later date, if I discover their whereabouts, I will update the scripts - unless someone can point me in the right direction? In any case, I don't ever remember seeing these options set to anything other than the default values (probably because they are so well hidden)

Code

Stored Procedure:

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

Test Code:

USE AdventureWorksDW

GO

EXEC dbo.usp_GenerateCreateDropIndexCommands NULL

EXEC dbo.usp_GenerateCreateDropIndexCommands 'FactInternetSalesReason'