How to Script Out Indexes from SQL Server

snapshot-indexes-littlekendra.com

Sometimes you need to script out all the indexes in a database.

Maybe you’re concerned something has changed since they were last checked in.

Or maybe the indexes aren’t checked into source control, and you’re working on fixing that. (Important!)

Either way, sometimes you need to do it, and it’s not fun through the GUI. I needed to write some fresh demo code for this recently, and I needed it to give the details for partitioned tables using data compression, and I thought I’d share.

Enjoy! If you’ve got ideas about ways to improve this, I’d love to hear them in the comments.

TSQL For Scripting Out All Indexes in a Database

This will script out:

  • Clustered and nonclustered indexes (including filtered indexes)
  • The filegroup the index is created on (if not partitioned)
  • The partition scheme the index is created on (if partitioned)
  • Compression settings if the index is compressed– and it’s per partition if the table is partitioned
  • For tables that don’t have a clustered index, they will still get a row in the results with a comment indicating it is a heap

The code is available in a gist.

Previous Post
Why Table Partitioning Doesn’t Speed Up Query Performance (video)
Next Post
Indexes: Do Included Columns Always Go on a Non-Root Page? (video)

Related Posts

35 Comments. Leave new

  • Aaron Bertrand
    May 5, 2016 8:06 am

    Nice Kendra, one suggestion, add QUOTENAME() around table/schema/index/column names. Otherwise people with poor naming conventions will complain that it breaks. 🙂

    Reply
  • Hi Kendra,

    Thanks for the great script

    I was getting some ‘duplicate’ rows where tables and indexes with the same name existed in different databases on the same server, presumably because sys.dm_db_index_usage_stats is scoped at server level, rather than database level (unlike the other sys tables referenced in the script)

    Updating Line 68 with this additional criteria on DB_ID() removed the ambiguity:

    LEFT JOIN sys.dm_db_index_usage_stats AS stat ON si.object_id=stat.object_id and si.index_id=stat.index_id AND stat.database_id = DB_ID()

    Reply
  • Kendra,
    This is helpful to check also with different versions of database.
    Thank you,

    Reply
  • This can also be done using the Generate Scripts feature in SSMS: right click on a database, click Tasks, click Generate Scripts… and walk through the wizard, be sure on the Set Scripting Options click on the Advanced button and select Script Indexes=True to include the indexes. This an be done on all tables or specific tables of your choosing, and you can direct the output in a number of ways as well.

    Reply
    • Hi Marcy,

      The output from Generate Scripts is not quite the same. Scripting from Generate Scripts will NOT contain any data compression settings that you’ve applied on the index, whether on individual partitions or the whole index. I’ve heard some people express frustration over that, which is why I tried to cover it in this TSQL approach. (If there’s some way to get it to generate that, I’d love to learn how!)

      This script also doesn’t cover some things that it does by default in the Generate Scripts task. This TSQL doesn’t cover the table definition itself, pad index settings, etc. I could work that stuff in if there’s demand for it.

      Kendra

      Update – I have recently learned you can change the options for scripting from object explorer in SSMS to include scripting out things like compression. You just need to know to go into your options and do it.

      Reply
  • Maybe, as you’ve already pulled the FILLFACTOR, you might be so kind as to include it in the ALTER/CREATE when not in (0,100). With nearly 500 of over 4700 indexes to edit, if I had time……

    Thanks for the hard work putting this into a very usable format that Excel had no issues with!

    Reply
    • I like that idea! I’ll work on that.

      Reply
      • Although the formatting is messed up by posting here, does this work for you re adding FILLFACTOR to the WITH?

        /* with clause – fillfactor and compression go here */
        CASE
        WHEN row_compression_partition_list IS NOT NULL
        OR page_compression_partition_list IS NOT NULL
        THEN N’ WITH (‘–
        + —
        CASE
        WHEN si.fill_factor NOT IN ( 0, 100 )
        THEN N’FILLFACTOR = ‘+CONVERT( nvarchar(3), si.fill_factor)+N’, ‘–
        ELSE N”
        END —
        + —
        CASE
        WHEN row_compression_partition_list IS NOT NULL
        THEN N’DATA_COMPRESSION = ROW ‘–
        + —
        CASE
        WHEN psc.name IS NULL
        THEN N”
        ELSE–
        +N’ ON PARTITIONS (‘+row_compression_partition_list+N’)’
        END
        ELSE N”
        END–
        + —
        CASE
        WHEN row_compression_partition_list IS NOT NULL
        AND page_compression_partition_list IS NOT NULL
        THEN N’, ‘
        ELSE N”
        END–
        + —
        CASE
        WHEN page_compression_partition_list IS NOT NULL
        THEN N’DATA_COMPRESSION = PAGE ‘–
        + —
        CASE
        WHEN psc.name IS NULL
        THEN N”
        ELSE —
        +N’ ON PARTITIONS (‘+page_compression_partition_list+N’)’
        END
        ELSE N”
        END —
        +N’)’
        ELSE —
        CASE
        WHEN si.fill_factor NOT IN ( 0, 100 )
        THEN N’ WITH (FILLFACTOR = ‘+CONVERT(nvarchar(3), si.fill_factor)+N’)’–
        ELSE N”
        END —
        END —
        +

        Reply
  • […] Kendra Little provides a T-SQL script to script out all indexes on a database: […]

    Reply
  • Nicolas Reid
    May 9, 2016 8:56 am

    This is my version of almost the same, except it’s just for giving an overview instead of create statements. It helps me with keeping an overview over the 90 thousand databases, the oldest from 2002.

    One nice aspect is it shows the actual column and include definition, including ascending or descending. You did know that Sql Server is perfectly happy to allow you to create a duplicate index, didn’t you?

    One not so nice thing is that Sql Server is not able to tell you the create date of an index.(Believe me, it really, really can’t!)

    select st.name as [table],
    si.name as [index],
    si.type_desc,
    nullif((iif(iif(isnull(ddius.last_user_seek,-1) >= isnull(ddius.last_user_scan,-1), isnull(ddius.last_user_seek,-1), isnull(ddius.last_user_scan,-1)) >= isnull(ddius.last_user_lookup,-1), iif(isnull(ddius.last_user_seek,-1) >= isnull(ddius.last_user_scan,-1), isnull(ddius.last_user_seek,-1), isnull(ddius.last_user_scan,-1)), isnull(ddius.last_user_lookup,-1))), -1)
    as [last_user_usage],
    ddius.user_seeks, ddius.user_scans, ddius.user_lookups, ddius.user_updates,
    si.is_primary_key,
    si.is_unique,
    si.is_unique_constraint,
    si.ignore_dup_key,
    si.has_filter,
    si.fill_factor,
    si.allow_row_locks,
    si.allow_page_locks,
    si.is_disabled,
    stuff((select ‘,’ + sc.name + case when sic.is_descending_key=1 then N’↓’ else N” end from sys.index_columns sic join sys.columns sc on sc.object_id=sic.object_id and sc.column_id=sic.column_id where sic.object_id=si.object_id and sic.index_id=si.index_id and sic.is_included_column=0 order by sic.is_included_column,sic.key_ordinal for xml path(”)),1,1,”)
    as [index_columns],
    isnull(stuff((select ‘,’ + sc.name from sys.index_columns sic join sys.columns sc on sc.object_id=sic.object_id and sc.column_id=sic.column_id where sic.object_id=si.object_id and sic.index_id=si.index_id and sic.is_included_column=1 order by sic.is_included_column,sic.key_ordinal for xml path(”)),1,1,”),”)
    as [index_included_columns]
    from
    sys.indexes si
    join sys.tables st
    on st.object_id=si.object_id
    left outer join sys.dm_db_index_usage_stats ddius
    on si.object_id = ddius.object_id
    and si.index_id = ddius.index_id
    and ddius.database_id = db_id()
    order by
    st.name,si.name

    Reply
    • “One nice aspect is it shows the actual column and include definition, including ascending or descending. You did know that Sql Server is perfectly happy to allow you to create a duplicate index, didn’t you?”

      Yep… SQL Server would even suggest duplicate indexes in the Missing Indexes Feature up until SQL Server 2012. I filed a bug on that back in 2009 that was fixed. But of course you can still create them.

      I did have the script show DESC columns (ASC is the default), I’m not clear on whether you didn’t look at the script or if you’re saying there’s a bug in the script?

      Reply
  • […] How to Script Out Indexes from SQL Server […]

    Reply
  • Great script Kendra! Adding other index types and with parameter options in index_create_statement would make it complete.

    Reply
  • Hello Kendra,

    I am using this script in my current project to backup CL and NC indexes. I want to run it for all DBs in just one go. I tried few ways like using cursor, msforeachdb, and temp table but did not succeed. Do you have an easy way to do this?

    Reply
    • Please tell how your attempts didn’t succeed.
      I do the same using Linked Servers (I have a multi-server situation). In the outer script-loop I first create a central discretely named results table in my personal administrator database to catch the results in, then I build dynamic SQL-strings and then “EXEC(@SQLscript) AT MyLinkedServer”. These SQL-strings are built to insert their results into the central table.
      My script loops through all databases registered in our central database, where all databases are registered at which server they reside and their name DB_NAME.
      I have name d my script the ‘BrickLoop’, since it loops over all indicated homogeneous databases over all our servers (bricks I call them).
      It needs some investment, but it works a wonder since I devised it over 12 years ago.

      The essential tricks are:
      1. first create a centrally located catching table for all results
      2. build dynamic SQL-strings in a loop
      3. loop over your databases with “EXEC @SqlString AT My LinkedServer”
      (the @SqlString must insert into the central table, obviously)
      4. regard your results in the central table
      5. Voila!

      TIP: use “RAISERROR(@Msg,0,1) WITH NOWAIT” to force verbose progress output during the loop over all databases.

      Often my BrickLoop takes several hours to complete for our 190.000 databases, but then I do have complete and concise results.

      Reply
      • Did you say 190,000 databases? Just curious– how many instances do you have them spread across, and how long is SQL Server startup time on the instances that have the most databases?

        (Not asking related to the current topic, just always fascinated by instances with lots of databases.)

        Reply
  • Hi Kendra,

    Very nice it only also creates an “CREATE UNIQUE NONCLUSTERED INDEX” record for indexes with is_unique_constraint = 1.

    Should it for those not create a “ADD CONSTRAINT [] UNIQUE NONCLUSTERED” record?

    Still very nice you helpt me a lot…

    Reply
  • Great script. Just wondering if you add a IF NOT EXISTS? Also maybe a companion script that does a DROP?

    Reply
  • Thank you Kendra.

    I had a problem that someone had dropped some indexes from dev.

    I ran this script in dev and production and copied the outputs of the index_create_statement columns from both into txt files. I then loaded these into tables and ran a query to identify what was missing from dev compared to prod.

    SELECT index_create_statement as missing_dev FROM prod_indexes
    EXCEPT
    SELECT * FROM dev_indexes

    It saved me a lot of work.

    Thanks

    Reply
  • hi,

    but any post you all tried, the most accurate one, shows the duplicated index by index column and missing index?

    Reply
    • I’m not sure what you mean by your question– could you perhaps restate it?

      Reply
      • sorry, I think there are some typo here. I mean this kind of script is good and I am not sure if you can share script to find out unused index and missing index, the script is perfect if it also generate the respective create and drop statement for the indexes.

        Reply
  • Thank you for a great script.

    Reply
  • Hi Kendra,

    Thank you for a great script. Any chance this could be modified to included columnstore indexes as well?

    Reply
  • Super Script..excellent…really same my time.

    Reply
  • Thanks for sharing Kendra, I found this super useful the other day – with a small tweak I used it to script out missing clustered indexes on a bunch of databases where they had been upgraded through versions but clustered indexes had been skipped, I know you can use the GUI etc but where this shined was being able to loop through every database and compare against a base schema – was awesome!

    Reply
  • […] TSQL For Scripting Out All Indexes in a Database […]

    Reply
  • Hi I would add AND t.is_ms_shipped = 0 on line 157. I don’t think anyone needs to script out Microsoft generated scripts.

    Reply
  • Thank you for a great script!

    Reply

Leave a Reply

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

Menu