Cool Tips‎ > ‎Indexes‎ > ‎

Indexes - 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'


ċ
usp_GenerateCreateDropIndexCommands.sql
(4k)
Andy Hughes,
Jul 8, 2012, 2:31 PM
Comments