Script All Indexes SQL Server 2005/2008

I needed a script to script out all the indexes with their included columns. All the scripts I could find on the inernet only scripted the normal columns, so I created my own script to script all columns and included columns.

At the moment this script does not support Fill Factor, Filters, With Options or File Groups.

If and when I make any changes I will update this script. If you like you can make changes and send them to me and I’ll update accordingly

ALTER PROC ScriptOutIndexes
AS
SET NOCOUNT ON
SELECT
 OBJECT_NAME([ixs].[object_id]) 'table_name'
 ,[ixs].[type] 'index_type'
 ,[ixs].[type_desc]
 ,[ixs].[name] 'index_name'
 ,[ixs].[is_unique]
 ,[ixs].[fill_factor]
 ,[ix_col].[key_ordinal]
 ,[col].[name] 'column_name'
 ,[ix_col].[is_descending_key]
 ,[is_included_column]
INTO
 #Indexes
 FROM
 [sys].[tables] tbls
 INNER JOIN [sys].[indexes] ixs
 ON [tbls].[object_id] = [ixs].[object_id]
 AND [tbls].[type] = 'U'
 INNER JOIN [sys].[index_columns] ix_col
 ON [ixs].[object_id] = [ix_col].[object_id]
 AND [ixs].[index_id] = [ix_col].[index_id]
 INNER JOIN [sys].[columns] col
 ON [col].[object_id] = [ix_col].[object_id]
 AND [col].[column_id] = [ix_col].[column_id]
 WHERE
 [ixs].[type] IN ( 1 , 2 )
 ORDER BY
 OBJECT_NAME([ixs].[object_id])
 ,[ixs].[type]
 ,[ixs].[name]
 ,[is_included_column]
 ,[ix_col].[key_ordinal]
CREATE TABLE #CreateStatements
 (
 SQLStatement VARCHAR(MAX)
 )
DECLARE @prev_table_name VARCHAR(255)
 DECLARE @table_name VARCHAR(255)
 DECLARE @type_desc VARCHAR(255)
 DECLARE @index_name VARCHAR(255)
 DECLARE @prev_index_name VARCHAR(255)
 DECLARE @is_unique VARCHAR(255)
 DECLARE @fill_factor VARCHAR(255)
 DECLARE @column_name VARCHAR(255)
 DECLARE @is_descending_key VARCHAR(255)
 DECLARE @is_included_column VARCHAR(255)
 DECLARE @included_column_set BIT
 DECLARE @create_statement VARCHAR(MAX)
DECLARE IX_Cursor CURSOR
 FOR SELECT
 [table_name]
 ,[type_desc]
 ,[index_name]
 ,[is_unique]
 ,[fill_factor]
 ,[column_name]
 ,[is_descending_key]
 ,[is_included_column]
 FROM
 [#Indexes]
OPEN IX_Cursor
 FETCH NEXT FROM IX_Cursor INTO  
 @table_name,
 @type_desc,
 @index_name,
 @is_unique,
 @fill_factor,
 @column_name,
 @is_descending_key,
 @is_included_column
SET @prev_table_name = ''
 SET @prev_index_name = ''
 SET @included_column_set = 0
WHILE @@FETCH_STATUS = 0
 BEGIN
 --PRINT @table_name + ' ' + @index_name + ' ' + @prev_index_name
 IF @prev_index_name != @index_name
 BEGIN
 SET @create_statement = ''
 SET @create_statement = @create_statement + 'IF NOT EXISTS (SELECT [Name] FROM [sys].[indexes] WHERE [name] = ''' + @index_name + ''') '  
 SET @create_statement = @create_statement + 'CREATE'
 SET @create_statement = @create_statement + CASE WHEN @is_unique = '1' THEN ' UNIQUE '
 ELSE ' '
 END
 SET @create_statement = @create_statement + @type_desc
 SET @create_statement = @create_statement + ' INDEX '
 SET @create_statement = @create_statement + @index_name
 SET @create_statement = @create_statement + ' ON ['
 SET @create_statement = @create_statement + @table_name  
 SET @create_statement = @create_statement + '] ( ['
 SET @create_statement = @create_statement + @column_name
 SET @create_statement = @create_statement + CASE WHEN @is_descending_key = '1' THEN '] DESC'
 ELSE ']'
 END
 SET @create_statement = @create_statement + ', ['
SET @prev_index_name = @index_name     
 END
 ELSE
 BEGIN
 IF @prev_index_name = @index_name
 IF @is_included_column = '0'
 BEGIN
 SET @create_statement = @create_statement + @column_name
 SET @create_statement = @create_statement + CASE WHEN @is_descending_key = '1' THEN '] DESC'
 ELSE ']'
 END
 SET @create_statement = @create_statement + ', ['
 END
 ELSE
 BEGIN
 IF @included_column_set = 0
 SET @create_statement = SUBSTRING(@create_statement , 1 , LEN(@create_statement) - 3) + ')'
IF @is_included_column = '1'
 BEGIN
 IF @included_column_set = 0
 BEGIN
 SET @create_statement = @create_statement + ' INCLUDE ( ['
 SET @included_column_set = 1
 END
 SET @create_statement = @create_statement + @column_name
 SET @create_statement = @create_statement + '], ['
 END
 ELSE
 SET @prev_index_name = @index_name     
 END
 END
FETCH NEXT FROM IX_Cursor INTO  
 @table_name,
 @type_desc,
 @index_name,
 @is_unique,
 @fill_factor,
 @column_name,
 @is_descending_key,
 @is_included_column
IF @prev_index_name != @index_name
 BEGIN
 SET @create_statement = SUBSTRING(@create_statement , 1 , LEN(@create_statement) - 3) + ' )'
 SET @included_column_set = 0
 INSERT  INTO #CreateStatements
 VALUES
 ( @create_statement )
 END
END
CLOSE IX_Cursor
 DEALLOCATE IX_Cursor
SELECT
 SQLStatement
 FROM
 #CreateStatements