Finding Database Indexes: “They’re coming outta the walls! They’re coming outta the goddamn walls!”

As someone who deals with SQL Server replication “on the regs” I’m no stranger to replication snapshots. Dealing with snapshots (wanted or otherwise) means I’m probably going to need to re-apply indexes once it’s finished. I can’t actually do that unless I know what indexes exist for a given set of tables (or even views) unless I script them out ahead of time.

The thing is: just how many indexes do I actually have? Even if you’re not having to deal with stripping them off and/or putting them back, even just knowing what your index situation is can be mighty helpful. So strap in, enjoy the “Aliens” quotes, and read about my favorite script in today’s contribution to #tsql2sday which was hosted by Bert Wagner.

“Yo! Stop your grinnin’, and drop your linen! Found ’em!”

The premise is simple: it will generate a series of DROP and then CREATE INDEX commands for every index. The process is a little more complex in practice, but at a high level it:

1. Creates a special schema to house a temporary object,
2. Creates a special stored procedure to run the code,
3. Calls said stored procedure,
4. Generates a bunch of PRINT statements that serve as the output (along with new line support for readability),
5. Cleans up the stored procedure it generated,
6. And finally deletes the schema it created.

Nifty.

I can’t take all the credit (or even much credit) for this script: I found it ages ago over on SQL Server Central’s Script’s repository, and it was an already modified version of a script that was written and posted on the SQL Solace blog way back in 2010. I’ve made a few small tweaks to it (mostly formatting related) but otherwise it’s mostly unchanged. I love it though, and it’s served me well through the years. Here’s my version of it:

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'index_maint' AND SCHEMA_OWNER = 'dbo')
BEGIN
    EXEC('CREATE SCHEMA index_maint')
END
GO

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'GenerateIndexesScript' AND ROUTINE_TYPE = N'PROCEDURE')
BEGIN
    EXEC ('CREATE PROCEDURE [index_maint].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
END
GO

ALTER PROCEDURE index_maint.GenerateIndexesScript
(
    @IncludeFileGroup  bit = 1,
    @IncludeDrop       bit = 1,
    @IncludeFillFactor bit = 1
)
AS

BEGIN
    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)

    OPEN Indexes_cursor

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

    WHILE (@@FETCH_STATUS = 0)
    BEGIN

        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 '

        IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
            BEGIN
                SET @sCreateSql = @sCreateSql + 'UNIQUE '
            END
        IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
            BEGIN
                SET @sCreateSql = @sCreateSql + 'CLUSTERED '
            END

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

        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

        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
                        IF (@IxIsIncl = 0) AND (@IxIncl = 1)
                            BEGIN
                                SET @IxIsIncl = 1
                                SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
                            END
                        ELSE
                            BEGIN
                                SET @sCreateSql = @sCreateSql + ',' + @NewLine
                            END
                    END

                SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
                IF @IxIsIncl = 0
                    BEGIN
                        IF @Desc = 1
                            BEGIN
                                SET @sCreateSql = @sCreateSql + ' DESC'
                            END
                        ELSE
                            BEGIN
                                SET @sCreateSql = @sCreateSql + ' ASC'
                            END
                    END
                FETCH NEXT FROM IndexColumns_cursor
                INTO @IxColumn, @IxIncl, @Desc
            END
        CLOSE IndexColumns_cursor
        DEALLOCATE IndexColumns_cursor

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

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


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

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

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

        PRINT @sCreateSql
        PRINT 'GO' + @NewLine  + @NewLine

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

END
GO

EXEC index_maint.GenerateIndexesScript 1, 0, 1
GO

DROP PROCEDURE [index_maint].[GenerateIndexesScript]
GO

DROP SCHEMA [index_maint]
GO

“Every meal’s a banquet. Every paycheck a fortune! Every formation’s a parade!”

Here’s the thing: while I rely on this script to generate my indexes for my replicas, I can use this script for almost everything related to every index in a database.  The script itself is very useful for when you need to script them out, sure… but I like to run it from time to time on my databases to get a pulse check of just how many indexes exist. After all, no indexes are (usually) bad, and too many can be just as bad. But you don’t know what you don’t know, as they say, so even if you don’t plan on actually doing anything with indexes, just having this in your hip pocket is good. From here, once you get a check of what’s there, maybe you could use an index usage script to find ones to drop, assuming you have a lot.

Oh, and what if you wanted to take a backup of just your index creation scripts? Same thing: you could schedule this to run, and I bet without too much modification, have it write the results to a table. That way, if indexes are changed or dropped, you can easily get them back to where they were.

“I like to keep this handy. For close encounters.” (Bonus PowerShell)

Of course, sometimes I need to move quick or add in some additional info in a hurry for times like that, I still rely on PowerShell. While you can’t easily get an automation “DROP INDEX” statement like the T-SQL above does, if you want to quickly script out all your indexes in a database, you can do so with just a few lines:

import-module sqlserver
$Database = Get-ChildItem -Path SQLSERVER:\SQL\servername\instance\databases\databasename
$Indexes = @()
$Indexes += $Database.Tables.Indexes
$Indexes += $Database.Views.Indexes
$Indexes.Script()

For this to work you’ll need to SQL Server PowerShell module from the PowerShell gallery, and replace “servername” with your server’s host name, “instance” with the name of your instance (or “default” if it’s a default/unnamed instance), and the name of your database in place of “databasename.” This will just output to the screen, but piping the results to something Out-File will work well too.