Script out the indexes in a database

I'm just about to begin a big performance improvement project. I'll be starting with an evaluation of indexes. This will involve synchronizing a development environment's indexes with those that are in live. I need to, however, keep a record of the existing indexes on that dev environment.

So I've written the code below. It will return a table with the object_id, table name, index name, index_id and a create index script. This script includes the drop and create statements for both primary keys and unique constraints. There are quite a few good scripts out in the wider web world, but I needed a few specific things so I just wrote my own.


There are a number of variables for both the run time and permanent index settings:

• Run time settings 
• @IncludeDropIfExists int = 0, --0 = False, 1 = True, 2 = Moving to new database
• @ONLINE varchar(3) = NULL, --NULL = SQL Server default setting; 'OFF' ; 'ON'
• @SORT_IN_TEMPDB varchar(3) = NULL, --NULL = SQL Server default setting; 'OFF' ; 'ON'
• @DROP_EXISTING varchar(3) = 'OFF', --'OFF' ; 'ON' Cannot be null
• Permanent settings
• @PAD_INDEX varchar(3) = NULL, --NULL = Existing setting; or override with: 'OFF' ; 'ON'
• @STATISTICS_NORECOMPUTE varchar(3) = NULL, --NULL = Existing setting; or override with: 'OFF' ; 'ON'
• @IGNORE_DUP_KEY varchar(3) = NULL, --NULL = Existing setting; or override with: 'OFF' ; 'ON'
• @ALLOW_ROW_LOCKS varchar(3) = NULL, --NULL = Existing setting; or override with: 'OFF' ; 'ON'
• @ALLOW_PAGE_LOCKS varchar(3) = NULL, --NULL = Existing setting; or override with: 'OFF' ; 'ON'
• @FILLFACTOR varchar(3) = NULL, --NULL = Existing setting; or override with: Any integer between 1 and 100 as char
• @FileGroup varchar(255) = 'PRIMARY'  --NULL = database default filegroup

All the variables apart from @IncludeDropIfExists relate to SQL Server settings. the @IncludeDropIfExists determines whether an if exists drop index or drop constraint statement is scripted for the indexes. There is also a @DROP_EXISTING variable. The two should not be both  set to ON or True as if you drop the index and then try to create it with the DROP_EXISTING set to ON the create index statement will fail. These two variables cannot be NULL.

For all the other variables if set to NULL they will either use the default SQL Server setting (in the case of the run time settings) or use the existing index setting (in the case of the permanent settings). You can override these by specifying a value. Each variable has instructions in the comment after it.

Here's the script which works on SQL Server 2005, 2008 and 2012 (I heard that it wasn't always possible to copy the script from the blog so I've made it available for download):