bradleykronson
5/25/2014 - 1:40 PM

This SP makes use of the function to get index details. Returns 2 lists of commands: Create all Indexes in a database using the defin

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

Returns 2 lists of commands:

Create all Indexes in a database using the definitions already in place in the database
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)

usp_GenerateCreateDropIndexCommands.sql
content_copyfile_download
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


/*
USE AdventureWorksDW
GO

EXEC dbo.usp_GenerateCreateDropIndexCommands NULL
EXEC dbo.usp_GenerateCreateDropIndexCommands 'FactInternetSalesReason'

EXEC dbo.usp_GenerateCreateDropIndexCommands 'Calendar'

*/