6 thoughts on “Listing all indexes

  1. user

    Here is a script that will return SQL statements to recreate all the indexes in a database.

    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('Person.Address') --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('Person.Address') --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('Person.Address') --Comment for all tables
               --AND I.name = 'IX_Address_PostalCode' --comment for all indexes 
    
    Reply
  2. user

    Here’s an example of the kind of query you need:

    select 
        i.name as IndexName, 
        o.name as TableName, 
        ic.key_ordinal as ColumnOrder,
        ic.is_included_column as IsIncluded, 
        co.[name] as ColumnName
    from sys.indexes i 
    join sys.objects o on i.object_id = o.object_id
    join sys.index_columns ic on ic.object_id = i.object_id 
        and ic.index_id = i.index_id
    join sys.columns co on co.object_id = i.object_id 
        and co.column_id = ic.column_id
    where i.[type] = 2 
    and i.is_unique = 0 
    and i.is_primary_key = 0
    and o.[type] = 'U'
    --and ic.is_included_column = 0
    order by o.[name], i.[name], ic.is_included_column, ic.key_ordinal
    ;
    

    This one is somewhat specific to a certain purpose (I use it in a little C# app to find duplicate indexes and format the output so it’s actually readable by a human). But you could easily adapt it to your needs.

    Reply
  3. user

    You could reference sysindexes

    Another trick is to look at the text of sp_helpindex to see how it reconstructs information from the underlying tables.

    sp_helptext 'sp_helpindex'
    

    I don’t have a reference for this, but I believe constraints are not stored in sysobjects because they are a different kind of thing; sysindexes contains meta-data about objects in sysobjects.

    Reply
  4. user

    If you need more information, here is a nice SQL script, which I use from time to time:

    DECLARE @TabName varchar(100)
    
    CREATE TABLE #temp (
       TabName varchar(200), IndexName varchar(200), IndexDescr varchar(200), 
       IndexKeys varchar(200), IndexSize int
    )
    
    DECLARE cur CURSOR FAST_FORWARD LOCAL FOR
        SELECT name FROM sysobjects WHERE xtype = 'U'
    
    OPEN cur
    
    FETCH NEXT FROM cur INTO @TabName
    WHILE @@FETCH_STATUS = 0
        BEGIN
            INSERT INTO #temp (IndexName, IndexDescr, IndexKeys)
            EXEC sp_helpindex @TabName
    
            UPDATE #temp SET TabName = @TabName WHERE TabName IS NULL
    
            FETCH NEXT FROM cur INTO @TabName
        END
    
    CLOSE cur
    DEALLOCATE cur
    
    DECLARE @ValueCoef int
    SELECT @ValueCoef = low FROM Master.dbo.spt_values WHERE number = 1 AND type = N'E'
    
    UPDATE #temp SET IndexSize = 
        ((CAST(sysindexes.used AS bigint) * @ValueCoef)/1024)/1024
            FROM sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
                INNER JOIN #temp T ON T.TabName = sysobjects.name AND T.IndexName = sysindexes.name
    
    SELECT * FROM #temp
    ORDER BY TabName, IndexName 
    
    DROP TABLE #temp
    
    Reply
  5. user

    I do not have a clear explanation why indexes are not stored in sys.objects. But I would like to contribute to find a simple way to list all indexes for all tables and views in a database. The following query retrieves all indexes including their type and also their object id and object type.

    use /*Enter here your database*/
    go
    select A.Object_id,B.name,B.type,B.type_desc, A.index_id,A.type,A.type_desc
    from sys.indexes A left join sys.objects B on A.object_id=B.object_id
    where B.type = 'U' or B.type='V' /*filtering on U or V to retrieve tables and views only*/
    order by B.name ASC /*Optional sorting*/
    
    Reply
  6. user

    I’ve written this code to iterate through all the databases in your server and push it to a table in a database named Maintenance. You should create this database first and then create a table in that database with the following fields:

    CREATE TABLE [dbo].[DBCC_Stats](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [DatabaseName] [varchar](50) NULL,
        [SchemaName] [nvarchar](128) NULL,
        [TableName] [sysname] NOT NULL,
        [StatName] [nvarchar](128) NULL,
        [modification_counter] [bigint] NULL,
        [rows] [bigint] NULL,
        [rows_sampled] [bigint] NULL,
        [% Rows Sampled] [bigint] NULL,
        [last_updated] [datetime2](7) NULL,
        [DateEntered] [datetime] NULL,
     CONSTRAINT [PK_DBCC_Stats] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[DBCC_Stats] ADD  CONSTRAINT [DF_DBCC_Stats_DateEntered]  DEFAULT (getdate()) FOR [DateEntered]
    

    To use the stored procedure below you’d pass in the server name.
    usp_Execute_Stats ‘[YourServerName]’

    `CREATE PROCEDURE usp_Execute_Stats
        @ServerName varchar(100)
    AS
    BEGIN
    DECLARE @strSQL varchar(max)
    SET @strSQL='USE ?
    SELECT      ''' + '?' + ''' AS DatabaseName,OBJECT_SCHEMA_NAME(obj.object_id) SchemaName, obj.name TableName,
               stat.name StatName, modification_counter,
                [rows], rows_sampled, rows_sampled* 100 / [rows] AS [% Rows Sampled],
                last_updated
    FROM        ' + @ServerName + '.' + '?' + '.sys.objects AS obj
    INNER JOIN  ' + @ServerName + '.' + '?' + '.sys.stats AS stat ON stat.object_id = obj.object_id
    CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
    WHERE       obj.is_ms_shipped = 0
    ORDER BY    modification_counter DESC'
    INSERT INTO Maintenance.dbo.vwDBCC_Stats
    EXEC sp_MSforeachdb @strSQL
    --Delete older logs
    DELETE Maintenance.dbo.DBCC_Stats
        --WHERE DatabaseName IN('Master','Model','MSDB','TempDB')
        WHERE [DateEntered] < getdate()-14
    END`
    
    Reply

Leave a Reply

Your email address will not be published.