Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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?

share|improve this question
I assume you want to do this in pure T-SQL and not with DMO? – Cade Roux Jul 3 '12 at 20:42
@caderoux as long as I can run it in SSMS i'm happy. – DForck42 Jul 3 '12 at 20:47
This is actually incredibly complicated to do because of all of the options in indexing. consider the importance of key order, included fields, filters, index options, ASC/DESC for each field, etc...it's not simple to script out. The BEST way would be to use something like powershell to leverage the SMO object model for this. – JNK♦ Jul 3 '12 at 20:51
@jnk true, do you know of a powershell script for this? I've never done anything with powershell before. – DForck42 Jul 3 '12 at 21:04
@DForck42 I do have one but it's more complicated than what you need. If you have any familiarity with object oriented programming it's pretty easy. Here's a starter for you. – JNK♦ Jul 3 '12 at 23:48
show 1 more comment

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
@DForck42 Yes, what I typically do for scripting into automated version monitoring (as opposed to developer-driven version control) is use a third-party tool like APEXSqlScript (apexsql.com/sql_tools_script.aspx), then script into files and commit them to source control every night. This can be independent of whatever you actually use and simply montiors the actual state of the database. In many ways, regular version control doesn't adequately tell you when things got applied to what physical databases. – Cade Roux Jul 3 '12 at 21:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.