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

2 Answers 2

up vote 5 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

--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. –  Wind Raven Mar 13 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.