Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on documenting my databases, and I would like to create a list of all of the indexes in my database. The reason I want to do this is so that I can track changes to my indexes overtime. I currently have a spreadsheet with all of the indexes that have changed since i created that spreadsheet, but it doesn't have all of the indexes. Instead of having to script out each index I would like to be able to just generate the list. I played with the system views but I wasn't able to figure it out.

How can I generate a list of indexes and the create statement for each index?

share|improve this question
    
I assume you want to do this in pure T-SQL and not with DMO? –  Cade Roux Jul 3 '12 at 20:42
    
@caderoux as long as I can run it in SSMS i'm happy. –  DForck42 Jul 3 '12 at 20:47
    
This is actually incredibly complicated to do because of all of the options in indexing. consider the importance of key order, included fields, filters, index options, ASC/DESC for each field, etc...it's not simple to script out. The BEST way would be to use something like powershell to leverage the SMO object model for this. –  JNK Jul 3 '12 at 20:51
    
@jnk true, do you know of a powershell script for this? I've never done anything with powershell before. –  DForck42 Jul 3 '12 at 21:04
    
@DForck42 I do have one but it's more complicated than what you need. If you have any familiarity with object oriented programming it's pretty easy. Here's a starter for you. –  JNK Jul 3 '12 at 23:48

3 Answers 3

up vote 6 down vote accepted

I don't seem to have a script like this in my toolbox, but found some others in my search.

Have you already seen this script?

http://www.sqlservercentral.com/scripts/Indexing/31652/

It uses a cursor, which is probably not strictly necessary. But I typically can clean them up to avoid a cursor by using the FOR XML and potentially a quirky update to concatenate the strings.

Also found these:

http://www.sqlservercentral.com/Forums/Topic796512-391-1.aspx

http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx

share|improve this answer
    
@DForck42 Yes, what I typically do for scripting into automated version monitoring (as opposed to developer-driven version control) is use a third-party tool like APEXSqlScript (apexsql.com/sql_tools_script.aspx), then script into files and commit them to source control every night. This can be independent of whatever you actually use and simply montiors the actual state of the database. In many ways, regular version control doesn't adequately tell you when things got applied to what physical databases. –  Cade Roux Jul 3 '12 at 21:05

I found this answer on mssqltips and this is the link to the article I found there.

I am also posting the TSQL for possible link-rot in future.

T-SQL Script to Drop All SQL Server Indexes

DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR FOR
 SELECT schema_name(t.schema_id), t.name,  i.name 
 FROM sys.indexes i
 INNER JOIN sys.tables t ON t.object_id= i.object_id
 WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 and (is_primary_key=0 and is_unique_constraint=0)

OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

WHILE @@fetch_status = 0
BEGIN
 SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)
 PRINT @TSQLDropIndex
 FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END

CLOSE CursorIndexes
DEDEALLOCATE CursorIndexes

T-SQL Script to Create All SQL Server Indexes

declare @SchemaName varchar(100)declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)

declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name], t.name, ix.name,
 case when ix.is_unique = 1 then 'UNIQUE ' else '' END 
 , ix.type_desc,
 case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
 + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
 + case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
 + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
 + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
 + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
 , ix.is_disabled , FILEGROUP_NAME(ix.data_space_id) FileGroupName
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 and ix.is_primary_key=0 and ix.is_unique_constraint=0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 order by schema_name(t.schema_id), t.name, ix.name

open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(max)
 declare @IncludedColumns varchar(max)

 set @IndexColumns=''
 set @IncludedColumns=''

 declare CursorIndexColumn cursor for 
  select col.name, ixc.is_descending_key, ixc.is_included_column
  from sys.tables tb 
  inner join sys.indexes ix on tb.object_id=ix.object_id
  inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
  order by ixc.index_column_id

 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn

 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
   set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 

  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
 end

 close CursorIndexColumn
 deallocate CursorIndexColumn

 set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
 --  print @IndexColumns
 --  print @IncludedColumns

 set @TSQLScripCreationIndex =''
 set @TSQLScripDisableIndex =''
 set @TSQLScripCreationIndex='CREATE '+ @is_unique  +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+ 
  case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  

 if @is_disabled=1 
  set  @TSQLScripDisableIndex=  CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) 

 print @TSQLScripCreationIndex
 print @TSQLScripDisableIndex

 fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

end
close CursorIndex
deallocate CursorIndex

Hope this helps someone.

share|improve this answer

--Try

    SELECT ' CREATE ' +
           CASE 
                WHEN I.is_unique = 1 THEN ' UNIQUE '
                ELSE ''
           END +
           I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
           I.name + ' ON ' +
           SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
           KeyColumns + ' )  ' +
           ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
           ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
           CASE 
                WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
                ELSE ' PAD_INDEX = OFF '
           END + ',' +
           'FILLFACTOR = ' + CONVERT(
               CHAR(5),
               CASE 
                    WHEN I.fill_factor = 0 THEN 100
                    ELSE I.fill_factor
               END
           ) + ',' +
           -- default value 
           'SORT_IN_TEMPDB = OFF ' + ',' +
           CASE 
                WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
                ELSE ' IGNORE_DUP_KEY = OFF '
           END + ',' +
           CASE 
                WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
                ELSE ' STATISTICS_NORECOMPUTE = ON '
           END + ',' +
           ' ONLINE = OFF ' + ',' +
           CASE 
                WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
                ELSE ' ALLOW_ROW_LOCKS = OFF '
           END + ',' +
           CASE 
                WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
                ELSE ' ALLOW_PAGE_LOCKS = OFF '
           END + ' ) ON [' +
           DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
    FROM   sys.indexes I
           JOIN sys.tables T
                ON  T.object_id = I.object_id
           JOIN sys.sysindexes SI
                ON  I.object_id = SI.id
                AND I.index_id = SI.indid
           JOIN (
                    SELECT *
                    FROM   (
                               SELECT IC2.object_id,
                                      IC2.index_id,
                                      STUFF(
                                          (
                                              SELECT ' , ' + C.name + CASE 
                                                                           WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                                = 1 THEN 
                                                                                ' DESC '
                                                                           ELSE 
                                                                                ' ASC '
                                                                      END
                                              FROM   sys.index_columns IC1
                                                     JOIN sys.columns C
                                                          ON  C.object_id = IC1.object_id
                                                          AND C.column_id = IC1.column_id
                                                          AND IC1.is_included_column = 
                                                              0
                                              WHERE  IC1.object_id = IC2.object_id
                                                     AND IC1.index_id = IC2.index_id
                                              GROUP BY
                                                     IC1.object_id,
                                                     C.name,
                                                     index_id
                                              ORDER BY
                                                     MAX(IC1.key_ordinal) 
                                                     FOR XML PATH('')
                                          ),
                                          1,
                                          2,
                                          ''
                                      ) KeyColumns
                               FROM   sys.index_columns IC2 
                                      WHERE IC2.Object_id = object_id('TheTableName') --Comment for all tables
                               GROUP BY
                                      IC2.object_id,
                                      IC2.index_id
                           ) tmp3
                )tmp4
                ON  I.object_id = tmp4.object_id
                AND I.index_id = tmp4.index_id
           JOIN sys.stats ST
                ON  ST.object_id = I.object_id
                AND ST.stats_id = I.index_id
           JOIN sys.data_spaces DS
                ON  I.data_space_id = DS.data_space_id
           JOIN sys.filegroups FG
                ON  I.data_space_id = FG.data_space_id
           LEFT JOIN (
                    SELECT *
                    FROM   (
                               SELECT IC2.object_id,
                                      IC2.index_id,
                                      STUFF(
                                          (
                                              SELECT ' , ' + C.name
                                              FROM   sys.index_columns IC1
                                                     JOIN sys.columns C
                                                          ON  C.object_id = IC1.object_id
                                                          AND C.column_id = IC1.column_id
                                                          AND IC1.is_included_column = 
                                                              1
                                              WHERE  IC1.object_id = IC2.object_id
                                                     AND IC1.index_id = IC2.index_id
                                              GROUP BY
                                                     IC1.object_id,
                                                     C.name,
                                                     index_id 
                                                     FOR XML PATH('')
                                          ),
                                          1,
                                          2,
                                          ''
                                      ) IncludedColumns
                               FROM   sys.index_columns IC2 
                                      WHERE IC2.Object_id = object_id('TheTableName') --Comment for all tables
                               GROUP BY
                                      IC2.object_id,
                                      IC2.index_id
                           ) tmp1
                    WHERE  IncludedColumns IS NOT NULL
                ) tmp2
                ON  tmp2.object_id = I.object_id
                AND tmp2.index_id = I.index_id
    --WHERE  
            --I.is_primary_key = 0
      --     AND 
           --I.is_unique_constraint = 0
           AND T.name NOT LIKE 'mt_%'
           --AND I.name NOT LIKE 'mt_%'
               AND I.Object_id = object_id('TheTableName') --Comment for all tables
               --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 
    ORDER BY T.name,I.name



    --Index List

    --SELECT '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName,
    --       Ind.[name]                AS IndexName,
    --       SUBSTRING(
    --           (
    --               SELECT ', ' + AC.name
    --               FROM   sys.[tables] AS T
    --                      INNER JOIN sys.[indexes] I
    --                           ON  T.[object_id] = I.[object_id]
    --                      INNER JOIN sys.[index_columns] IC
    --                           ON  I.[object_id] = IC.[object_id]
    --                           AND I.[index_id] = IC.[index_id]
    --                      INNER JOIN sys.[all_columns] AC
    --                           ON  T.[object_id] = AC.[object_id]
    --                           AND IC.[column_id] = AC.[column_id]
    --               WHERE  Ind.[object_id] = I.[object_id]
    --                      AND Ind.index_id = I.index_id
    --                      AND IC.is_included_column = 0
    --               ORDER BY
    --                      IC.key_ordinal 
    --                      FOR
    --                      XML PATH('')
    --           ),
    --           2,
    --           8000
    --       )                         AS KeyCols,
    --       SUBSTRING(
    --           (
    --               SELECT ', ' + AC.name
    --               FROM   sys.[tables] AS T
    --                      INNER JOIN sys.[indexes] I
    --                           ON  T.[object_id] = I.[object_id]
    --                      INNER JOIN sys.[index_columns] IC
    --                           ON  I.[object_id] = IC.[object_id]
    --                           AND I.[index_id] = IC.[index_id]
    --                      INNER JOIN sys.[all_columns] AC
    --                           ON  T.[object_id] = AC.[object_id]
    --                           AND IC.[column_id] = AC.[column_id]
    --               WHERE  Ind.[object_id] = I.[object_id]
    --                      AND Ind.index_id = I.index_id
    --                      AND IC.is_included_column = 1
    --               ORDER BY
    --                      IC.key_ordinal 
    --                      FOR
    --                      XML PATH('')
    --           ),
    --           2,
    --           8000
    --       )                         AS IncludeCols
    --FROM   sys.[indexes] Ind
    --       INNER JOIN sys.[tables]   AS Tab
    --            ON  Tab.[object_id] = Ind.[object_id]
    --       INNER JOIN sys.[schemas]  AS Sch
    --            ON  Sch.[schema_id] = Tab.[schema_id]
    --                 WHERE Tab.name  = 'TheTableName' -- uncomment to get single table indexes detail
    --ORDER BY
    --       TableName
share|improve this answer
1  
A description of the code would really help this answer. –  WindRaven Mar 13 '14 at 20:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.