In previous posts I described techniques how to extract TRIGGER and DEFAULT constraint definitions using T-SQL scripting.  Today I will explain how I used database system objects to generate CREATE INDEX statements for my database.

The SQL Server Books Online provides extensive information on how to create an index.  This can be found in the MSDN article titled CREATE INDEX.  Reproducing one of the examples from this article, the syntax to create an index is as simple as:

USE AdventureWorks2008R2;
GO
CREATE INDEX IX_ProductVendor_VendorID
    ON Purchasing.ProductVendor (BusinessEntityID);
GO

In SQL Server 2005 and later versions, information about indexes is exposed mainly using two system catalog views, namely the sys.indexes and sys.tables.  Other objects which I used in the latest version of the script are the sys.columns, sys.index_columns, and sys.data_spaces catalog views.

The first part creates INNER JOINs between the sys.indexes, sys.tables and sys.data_spaces views to retrieve the index name, the table on which it has been created, the FILEGROUP storing the index pages, the index type (CLUSTERED or NONCLUSTERED) and other index properties.  The sys.columns and sys.index_columns are used to retrieve the indexed columns as well as any included columns.

The script is composed of two parts, three actually; the variable declarations and assignment, a query to generate CLUSTERED indexes, and the final query to generate NONCLUSTERED indexes.  The @NonClusteredIndexFileGroup variable is used to define the FILEGROUP which will store NONCLUSTERED index - if not set it will default to the FILEGROUP where the “parent” table is stored.

Scripts for SQL Server 2005 and 2008 can be obtained by using the links below.