List out and script all indexes in a database

by Posted on

Its quite common that when there is some upgrade, change or release in application, there are chances that our database objects get modified. It may drop some indexes too hampering performance. I had similar situation in my profession. I had creating multiple indexes to find tune database performance, but then there was an application patch release. All those indexes we manually created were dropped by the application. Soon, there was blocking, deadlocks, long running queries, high cpu and performance became pathetic. Later it was understood that all those custom index we DBAs manually created were mercilessly removed by the application. 😥

After this incident, for every application patch release or change, we made a habit of keeping track of all the indexes in the database. We use below script to pull out the report before and after the release activity. The report would help us review, compare and recreate dropped indexes. The output the script is in such a way that you could save all the create statements in a text file and you could paste the list of indexes into an excel sheet for reporting.

/*
Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )

Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com

        1) Changed Schema of routine to Utils
        2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
        3) Added Schemas to script
        4) Reformatted for clarity

Modifications 2012-May-04 R. Gosling
		1) Added in the Schema name to table name

Modifications 2015-Mar-12 Sherbaz.com
		1) Removed the script out of Stored procedure
		2) Outputs the index list as table which could be copied to Excel for reporting and comparing.

--  Usage: set variables @IncludeFileGroup, @IncludeDrop, @IncludeFillFactor and execute the whole script 
*/


declare @IncludeFileGroup  bit = 1,
	@IncludeDrop       bit = 1,
	@IncludeFillFactor bit = 1

set nocount on
declare @indexType varchar(30), @indexColumns varchar(300), @indexIncludedColumns varchar(300), @script varchar(max)

if exists (select top 1 * from tempdb.sys.all_objects where name like '#indexlist{88dc7f9385b2c8971f38421926506de3e3fc5e843c9520f0674d4b7de6ae62da}')
drop table #indexlist
create table #indexlist
(
	schemaName sysname,
	tableName sysname,
	tableId int,
	indexName sysname,
	indexId int,
	[fillFactor] int,
	columnNames varchar(300),
	includedColumnNames varchar(300),
	indexType varchar(100),
	script varchar(max)
)
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor CURSOR
        FOR SELECT SC.Name          AS      SchemaName,
                   SO.Name          AS      TableName,
                   SI.OBJECT_ID     AS      TableId,
                   SI.[Name]         AS  IndexName,
                   SI.Index_ID       AS  IndexId,
                   FG.[Name]       AS FileGroupName,
                   CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
              INNER JOIN sys.objects SO
                      ON SI.OBJECT_ID = SO.OBJECT_ID
              INNER JOIN sys.schemas SC
                      ON SC.schema_id = SO.schema_id
             WHERE OBJECTPROPERTY(SI.OBJECT_ID, 'IsUserTable') = 1
               AND SI.[Name] IS NOT NULL
               AND SI.is_primary_key = 0
               AND SI.is_unique_constraint = 0
               AND INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], 'IsStatistics') = 0
             ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

    DECLARE @SchemaName     sysname
    DECLARE @TableName      sysname
    DECLARE @TableId        int
    DECLARE @IndexName      sysname
    DECLARE @FileGroupName  sysname
    DECLARE @IndexId        int
    DECLARE @FillFactor     int

    DECLARE @NewLine nvarchar(4000)
    SET @NewLine = char(13) + char(10)
    DECLARE @Tab  nvarchar(4000)
    SET @Tab = SPACE(4)

    -- Loop through all indexes
    OPEN Indexes_cursor

    FETCH NEXT
     FROM Indexes_cursor
     INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

    WHILE (@@FETCH_STATUS = 0)
        BEGIN

			insert into #indexlist(schemaName, tableName, tableId, indexName, indexId, [fillFactor])
			values(@SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FillFactor)

            DECLARE @sIndexDesc nvarchar(4000)
            DECLARE @sCreateSql nvarchar(4000)
            DECLARE @sDropSql           nvarchar(4000)

            SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
            SET @sDropSql = 'IF EXISTS(SELECT 1' + @NewLine
                          + '            FROM sysindexes si' + @NewLine
                          + '            INNER JOIN sysobjects so' + @NewLine
                          + '                   ON so.id = si.id' + @NewLine
                          + '           WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
                          + '             AND so.[Name] = N''' + @TableName + ''')  -- Table Name' + @NewLine
                          + 'BEGIN' + @NewLine
                          + '    DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                          + 'END' + @NewLine

            SET @sCreateSql = 'CREATE '
			
			SET @script = '';
			set @script = 'CREATE '

			SET @indexType = ''
            -- Check if the index is unique
            IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'UNIQUE '
					set @indexType = 'UNIQUE '
					set @script = @script + 'UNIQUE '
                END
            --END IF
            -- Check if the index is clustered
            IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'CLUSTERED '
					SET @indexType = @indexType + 'CLUSTERED '
					set @script = @script + 'CLUSTERED '
                END
            --END IF

			update #indexlist set indexType = @indexType where tableId=@TableId and indexId=@IndexId

            SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

			SET @script = @script + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']('

            -- Get all columns of the index
            DECLARE IndexColumns_cursor CURSOR
                FOR SELECT SC.[Name],
                           IC.[is_included_column],
                           IC.is_descending_key
                      FROM sys.index_columns IC
                     INNER JOIN sys.columns SC
                             ON IC.OBJECT_ID = SC.OBJECT_ID
                            AND IC.Column_ID = SC.Column_ID
                     WHERE IC.OBJECT_ID = @TableId
                       AND Index_ID = @IndexId
                     ORDER BY IC.[is_included_column],
                              IC.key_ordinal

            DECLARE @IxColumn      sysname
            DECLARE @IxIncl        bit
            DECLARE @Desc          bit
            DECLARE @IxIsIncl      bit
            SET @IxIsIncl = 0
            DECLARE @IxFirstColumn   bit
            SET @IxFirstColumn = 1

			set @indexColumns = ''
			set @indexIncludedColumns = ''

            -- Loop through all columns of the index and append them to the CREATE statement
            OPEN IndexColumns_cursor
            FETCH NEXT
             FROM IndexColumns_cursor
             INTO @IxColumn, @IxIncl, @Desc

            WHILE (@@FETCH_STATUS = 0)
                BEGIN
                    IF (@IxFirstColumn = 1)
                        BEGIN
                            SET @IxFirstColumn = 0
                        END
                    ELSE
                        BEGIN
                            --check to see if it's an included column
                            IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                                BEGIN
                                    SET @IxIsIncl = 1
                                    SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
									set @indexIncludedColumns = @indexIncludedColumns + ' - ' + @IxColumn
									SET @script = @script + ')' + ' INCLUDE ('
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ',' + @NewLine
									set @indexColumns = @indexColumns + ' - ' + @IxColumn
									SET @script = @script + ', '
                                END
                            --END IF
                        END
                    --END IF

                    SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
					if @indexColumns = ''
					set @indexColumns = @indexColumns + ' - ' + @IxColumn

					SET @script = @script + @Tab + '[' + @IxColumn + ']'

                    -- check if ASC or DESC
                    IF @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
									set @indexColumns = @indexColumns + ':DESC'
									SET @script = @script + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
									set @indexColumns = @indexColumns + ':ASC'
									SET @script = @script + ' ASC'
                                END
                            --END IF
                        END
                    --END IF

					update #indexlist set columnNames = @indexColumns, includedColumnNames = @indexIncludedColumns
					where tableId = @TableId and indexId = @IndexId

                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

			SET @script = @script + ') '

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
					SET @script = @script + ' WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')'
                END
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
					SET @script = @script + 'ON ['+ @FileGroupName + ']'
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
					SET @script = @script
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'

            IF @IncludeDrop = 1
                BEGIN
                    PRINT @sDropSql
                    PRINT 'GO'
                END
            --END IF

            PRINT @sCreateSql

			set @script = @script
			update #indexlist set script = @script where tableId = @TableId and indexId = @IndexId

            PRINT 'GO' + @NewLine  + @NewLine

            FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END
    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor

	select * from #indexlist
	set nocount off

Published by Sherbaz

Technology enthusiast Sherbaz is a Microsoft certified SQL Server Subject Matter Expert. He had fun automating day to day task to achieve management goals. He has extensive hands-on experience in high availability deployments and support with several years of industry experience performing SQL Server installations, configuration, maintenance, backups, performance tuning, Transactional & P2P replication, migration, high availability solutions (Always on, Failover Clustering, Logshipping and Mirroring) and Disaster recovery. Sherbaz also has basic hands-on experience in other DBMSs like MongoDB, Oracle 9i, 10g, Sybase and MySQL. 1 year on Mongodb, Oracle 9i & 10g. Completed M102 & M202 courses in MongoDB. Sherbaz spends his spare time with automation in electronics, embedded system circuits and IOT in his private lab at home. Also, he supports his customers and users at www.SplitExpense.in for issues with app, website or telegram chat bot @SplitExpenseBot https://t.me/SplitExpenseBot. @TheSherBot https://t.me/TheSherBot is another telegram chat bot, partially artificial intelligent, programmed and maintained by him on python and backend Mongodb database. Sherbaz always showed interest learning new technologies and to think out of the box bringing innovation in work. Some of his work is documented @ www.sherbaz.com. He is now busy learning 3D modeling software on his roadmap towards building his next hobby build, A 3D PRINTER. Subscribe to his youtube channel https://www.youtube.com/c/SherbazMohamed/

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.