Generate T-SQL for Existing Indexes

Question

How do you programmatically generate T-SQL CREATE statements for existing indexes in a database? SQL Studio provides a "Script Index as->Create to" command that generates code in the form:

IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = N'IX_myindex')
    CREATE NONCLUSTERED INDEX [IX_myindex] ON [dbo].[mytable] 
    (
        [my_id] ASC
    )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO

How would you do this programmatically (ideally through Python)?

Answer 1

Generating it with T-SQL is a nightmare. If you are willing to use SMO and c# then take a look at SMO Script Index and FK's on a database

You can even call SMO from PowerShell so that might be another option.

Answer 2

Assuming you have 2005 or later, you can simply query sys.indexes for all of the information you need (like they're doing in the IF NOT EXISTS portion of the query).

If you're just looking for all indexes in a database just pull everything from that table within that database. If you're looking for indexes that meet certain criteria here's the list of fields in sys.indexes that you can use to filter on. The sys.index_columns table has all of the associated columns for each index as well.

Using this you should be able to get all the information you need about the indexes in your database.

This blog post has a pretty good SQL snippet that uses these tricks to interrogate the indexes in the database, and you may be able to use a fair bit of it to do what you want.