Script to Recreate All Indexes in SQL Server

For me, this particular code to generate a script for recreating indexes has its most practical use in a replication environment. For example, by default SQL Server replication often excludes non‑clustered indexes from subscribers, and later you may realize that some or all of those indexes are actually needed to maintain query performance on the subscriber databases. This script lets you quickly generate consistent index definitions across the publisher and subscribers without having to manually reverse‑engineer each index.

Also, generally speaking, index requirements for publisher and subscriber databases can be quite different. As a result, you may end up with different sets of indexes on the publisher and its subscribers.

Beyond replication, the script is also useful in several other common DBA scenarios. For instance, you can use it to recreate indexes after a schema‑only restore or when building dev/test environments from production schemas, ensuring that performance‑critical indexes are not accidentally omitted. It is also handy when migrating databases to new instances or clusters, or when documenting and auditing index layouts across multiple environments, giving you a repeatable, script‑based approach rather than relying on ad‑hoc hand‑written DDL.

/*
 ============================================================================
 INDEX SCRIPT GENERATOR (SQL Server 2005+)
 ============================================================================
 Purpose:
   Generates CREATE INDEX statements for all user‑defined indexes in the
   current database, including:
   - Clustered / nonclustered rowstore
   - Clustered / nonclustered columnstore
   - Filtered indexes
   - XML and spatial indexes

 Result:
   Outputs a column "CreateIndexStatement" containing full T‑SQL scripts.
   These can be pasted into a new window, run as‑is, or loaded into a temp
   table (e.g., #dba_index) for later use.

 Author:  
 Updated: 2026‑03‑26

 USAGE NOTES AND CAVEATS
 ----------------------------------------------------------------------------
 1. Database context:
    - This script runs in the context of the current database.
    - It only lists indexes in that database.

 2. Index types handled:
    - CLUSTERED / NONCLUSTERED (rowstore)
    - CLUSTERED_COLUMNSTORE / NONCLUSTERED_COLUMNSTORE
    - XML / SPATIAL
    - Filtered indexes (via WHERE clause)

 3. Indexes skipped:
    - Primary key / unique constraint indexes (is_primary_key = 1,
      is_unique_constraint = 1).
    - Hypothetical indexes (is_hypothetical = 1).
    - System objects (is_ms_shipped = 1).

 4. Collation‑handling:
    - Uses COLLATE DATABASE_DEFAULT on s.name, t.name, i.name to avoid
      collation conflicts when concatenating strings.
    - This assumes the database’ default collation is acceptable for all
      generated scripts.

 5. Generated statement behavior:
    - Wraps each CREATE INDEX in:
        IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE ...)
      to avoid duplicate index creation.
    - Uses OBJECT_ID with schema‑qualified names (e.g., [schema].[table]).
    - Includes key columns, ASC/DESC, included columns, and index options
      (PAD_INDEX, FILLFACTOR, ONLINE effects, etc.).

 6. What it does NOT do:
    - It does not script:
        - PRIMARY KEY / UNIQUE constraints (only pure indexes).
        - Statistics objects not backed by an index.
        - Columnstore ORDER(), MAXDOP=, ONLINE=, or other advanced options
          beyond the common WITH options listed.
    - It does not:
        - Drop indexes.
        - Handle partitioning syntax beyond the ON <filegroup | partition scheme>.
    - It does not preserve:
        - Existing index names on constraint‑created indexes (they are skipped).
        - Any index‑specific hints or undocumented options not exposed in
          sys.indexes.

 7. Safety and testing:
    - Always test output in a non‑production environment first.
    - In active OLTP systems, CREATE INDEX can block or slow down workloads;
      consider:
        - Using ONLINE = ON (if Enterprise Edition).
        - Creating indexes during maintenance windows.
        - Monitoring blocking and wait stats.
    - If you have collation‑specific comparisons elsewhere in your app,
      verify that the generated script’s collation semantics match.

 8. Compatibility:
    - Uses views introduced in SQL Server 2005 (sys.indexes, sys.index_columns,
      sys.stats, sys.data_spaces), so it works on 2005+.
    - ORDER BY CASE i.type_desc LIKE 'CLUSTERED%'... is SQL Server 2005+.
    - Some WITH options (e.g., ONLINE, DATA_COMPRESSION) may require SQL Server
      2008+ or 2012+ depending on index type.
    - Columnstore indexes require SQL Server 2012+ (Enterprise Edition for
      some features).

 9. Performance:
    - For very large databases with many tables and indexes, this query can
      be moderately heavy on catalog metadata; run it during low‑activity windows
      if that is a concern.

 ============================================================================ */

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

;WITH IncludedCols AS
(
    SELECT
        ic.object_id,
        ic.index_id,
        IncludedColumns =
            STUFF(
                (
                    SELECT ', ' + QUOTENAME(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
                    WHERE ic1.object_id = ic.object_id
                      AND ic1.index_id  = ic.index_id
                      AND ic1.is_included_column = 1
                    ORDER BY c.column_id
                    FOR XML PATH(''), TYPE
                ).value('.', 'nvarchar(max)')
            ,1,2,'')
    FROM sys.index_columns ic
    GROUP BY ic.object_id, ic.index_id
),
KeyCols AS
(
    SELECT
        ic.object_id,
        ic.index_id,
        KeyColumns =
            STUFF(
                (
                    SELECT ', ' + QUOTENAME(c.name) +
                           CASE WHEN 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
                    WHERE ic1.object_id = ic.object_id
                      AND ic1.index_id  = ic.index_id
                      AND ic1.is_included_column = 0
                    ORDER BY ic1.key_ordinal
                    FOR XML PATH(''), TYPE
                ).value('.', 'nvarchar(max)')
            ,1,2,'')
    FROM sys.index_columns ic
    GROUP BY ic.object_id, ic.index_id
)
SELECT
    'IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID(N''' +
        QUOTENAME(s.name COLLATE DATABASE_DEFAULT) +
        '.' +
        QUOTENAME(t.name COLLATE DATABASE_DEFAULT) +
        ''') AND name = N''' +
        i.name COLLATE DATABASE_DEFAULT +
        ''')
CREATE ' +
        CASE WHEN i.is_unique = 1
             THEN 'UNIQUE '
             ELSE ''
        END +
        CASE
            WHEN i.type_desc IN ('CLUSTERED', 'NONCLUSTERED')
                THEN i.type_desc + ' '
            WHEN i.type_desc LIKE '%COLUMNSTORE%'
                THEN i.type_desc + ' '
            WHEN i.type_desc IN ('XML', 'SPATIAL')
                THEN i.type_desc + ' '
            ELSE ''
        END + 'INDEX ' + QUOTENAME(i.name COLLATE DATABASE_DEFAULT) + ' ON ' +
        QUOTENAME(s.name COLLATE DATABASE_DEFAULT) + '.' +
        QUOTENAME(t.name COLLATE DATABASE_DEFAULT) +
        CASE
            WHEN i.type_desc LIKE '%COLUMNSTORE%' THEN
                ISNULL(' (' + kc.KeyColumns + ')','')
            ELSE
                ' (' + kc.KeyColumns + ')'
        END +
        ISNULL(' INCLUDE (' + ic.IncludedColumns + ')','') +
        ISNULL(' WHERE ' + i.filter_definition,'') +
        ' WITH (' +
            'PAD_INDEX = ' + CASE WHEN i.is_padded = 1 THEN 'ON' ELSE 'OFF' END +
            ', FILLFACTOR = ' + CONVERT(varchar(5), CASE WHEN i.fill_factor = 0 THEN 100 ELSE i.fill_factor END) +
            ', IGNORE_DUP_KEY = ' + CASE WHEN i.ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END +
            ', STATISTICS_NORECOMPUTE = ' + CASE WHEN st.no_recompute = 1 THEN 'ON' ELSE 'OFF' END +
            ', ALLOW_ROW_LOCKS = ' + CASE WHEN i.allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END +
            ', ALLOW_PAGE_LOCKS = ' + CASE WHEN i.allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END +
        ')' +
        ' ON [' + ds.name + '];'
AS CreateIndexStatement
FROM sys.indexes i
JOIN sys.tables t ON t.object_id = i.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_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 ds.data_space_id = i.data_space_id
LEFT JOIN IncludedCols ic ON ic.object_id = i.object_id
   AND ic.index_id  = i.index_id
LEFT JOIN KeyCols kc ON kc.object_id = i.object_id
   AND kc.index_id  = i.index_id
WHERE
    i.is_hypothetical      = 0        -- Exclude hypothetical indexes.
    AND i.index_id         > 0        -- Exclude heap row locators.
    AND i.is_primary_key   = 0        -- Skip primary key indexes.
    AND i.is_unique_constraint = 0    -- Skip unique constraint indexes.
    AND t.is_ms_shipped    = 0        -- Skip system tables.
ORDER BY
    s.name,
    t.name,
    CASE WHEN i.type_desc LIKE 'CLUSTERED%' THEN 0 ELSE 1 END,  -- Clustered first...
    i.name;                                                      -- then index name.

The script is thoroughly documented, so please review it carefully before running it, even in a test environment.

I welcome your comments, feedback, and suggestions for improvement.

Generate SQL Script To Recreate Indexes

DMV - Active queries sorted by CPU usage

select

           s.session_id,

           db_name(s.database_id) DB,

           s.login_time,

           s.host_name,

           s.program_name,

           s.login_name,

           s.cpu_time,

           s.memory_usage,

           s.total_scheduled_time,

           s.total_elapsed_time,

           s.endpoint_id,

           s.last_request_start_time,

           s.last_request_end_time,

           s.reads,

           s.writes,

           s.logical_reads,

           s.is_user_process,

           s.row_count,

           s.open_transaction_count,

           t.text as [command]

from sys.dm_exec_sessions s

inner join sys.dm_exec_connections c on c.session_id = s.session_id

cross apply sys.dm_exec_sql_text(most_recent_sql_handle) t

where s.session_id != @@spid

      and status = 'running'

order by cpu_time desc

---

And for older versions of SQL Server, the the following (Not Tested)

select

           db_name(a.dbid) as [db_name],

           hostname as [host_name],

           NT_USERNAME,

           spid,

           physical_io,

           cpu cpu2,

           db_name(a.dbid) as [database_name],

           program_name,

           status,

           text as [command]

From master..sysprocesses a

cross apply ::fn_get_sql(sql_handle) as command

where status = 'runnable'

--where db_name(a.dbid) like 'temp%'

order by cpu desc

Setup SQL Server Alert to get automatically notified of database blockings

Suppose you want to or need to know anytime your SQL Server is experiencing blocking where queries are being blocked and it may or may not escalate to a deadlock situation. Chances are you might be using a comprehensive tool to get performance alerts. But I don't always have access to such tools at the clients.

So here I have a SQL Server Agent alert to get automatic email when SQL Server detects queries being blocked.

There are basically two key alert settings I am using:

1.    The minimum threshold value for sending the email alert is 4. That means if more than 4 processes are involved in blocking an email is sent out immediately.

2.    The alert email shows what the current number of process being blocked is (I highlighted it in yellow in below alert). That way we can assess how quickly or slowly the blocking is getting worse.

After that first email alert, if the situation does not resolve itself, it will keep sending the email every 15 minutes with the updated count of processes being blocked.

So if we do not get a second email alert in next 15 minutes, it would mean it resolved itself and, no manual intervention is needed.

---

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'Alert on SQL Server SPIDs being blocked',

             @message_id=0,

             @severity=0,

             @enabled=1,

             @delay_between_responses=900,         -- This value is in seconds so 900 seconds = every 15 minutes

             @include_event_description_in=1,

             @notification_message=N'Pleae contact your DBA team at DBA-Team@YourOrganization.org for further assistance.',

             @category_name=N'[Uncategorized]',

             @performance_condition=N'General Statistics|Processes blocked||>|3',

             @job_id=N'00000000-0000-0000-0000-000000000000'

GO

EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'App_Team', @notification_method = 1

GO

EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'DBA', @notification_method = 1

GO

EXEC msdb.dbo.sp_update_notification @alert_name=N'Alert on SQL Server SPIDs being blocked', @operator_name=N'Pager Email', @notification_method = 1

GO

GO