HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

Generate create script for all indexes

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
scriptcreateallindexesgeneratefor

Problem

I am working on documenting my databases, and I would like to create a list of all of the indexes in my database. The reason I want to do this is so that I can track changes to my indexes overtime. I currently have a spreadsheet with all of the indexes that have changed since i created that spreadsheet, but it doesn't have all of the indexes. Instead of having to script out each index I would like to be able to just generate the list. I played with the system views but I wasn't able to figure it out.

How can I generate a list of indexes and the create statement for each index?

Solution

I don't seem to have a script like this in my toolbox, but found some others in my search.

Have you already seen this script?

http://www.sqlservercentral.com/scripts/Indexing/31652/

It uses a cursor, which is probably not strictly necessary. But I typically can clean them up to avoid a cursor by using the FOR XML and potentially a quirky update to concatenate the strings.

Also found these:

http://www.sqlservercentral.com/Forums/Topic796512-391-1.aspx

http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx

Context

StackExchange Database Administrators Q#20355, answer score: 8

Revisions (0)

No revisions yet.