I tweaked your code a bit:
CREATE PROCEDURE [dbo].[IndexLister]
AS
BEGIN
SET NOCOUNT ON
DECLARE
@idxTableName SYSNAME,
@idxTableID INT,
@idxname SYSNAME,
@idxid INT,
@colCount INT,
@IxColumn SYSNAME,
@IxFirstColumn BIT,
@ColumnIDInTable INT,
@ColumnIDInIndex INT,
@IsIncludedColumn INT,
@sIncludeCols VARCHAR(4000),
@sIndexCols VARCHAR(4000),
@sSQL VARCHAR(4000),
@sParamSQL VARCHAR(4000),
@location SYSNAME,
@IndexCountINT,
@CurrentIndexINT,
@CurrentColINT,
@NameVARCHAR(128),
@IsPrimaryKeyTINYINT,
@FillfactorINT
CREATE TABLE #IndexListing
(
[IndexListingID]INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[TableName]SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectID]INT NOT NULL,
[IndexName]SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexID]INT NOT NULL,
[IsPrimaryKey]TINYINT NOT NULL,
[FillFactor]INT
)
CREATE TABLE #ColumnListing
(
[ColumnListingID]INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
[ColumnIDInTable]INT NOT NULL,
[Name]SYSNAMECOLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnIDInIndex]INT NOT NULL,
[IsIncludedColumn]BIT NULL
)
INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR] )
SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor
FROM sys.indexes si
LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(si.object_id), si.index_id
SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1
WHILE @CurrentIndex <= @IndexCount
BEGIN
SELECT @idxTableName = [TableName],
@idxTableID = [ObjectID],
@idxname = [IndexName],
@idxid = [IndexID],
@IsPrimaryKey = [IsPrimaryKey],
@FillFactor = [FILLFACTOR]
FROM #IndexListing
WHERE [IndexListingID] = @CurrentIndex
-- So - it is either an index or a constraint
-- Check if the index is unique
IF (@IsPrimaryKey = 1)
BEGIN
SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY '
-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0)
BEGIN
SET @sSQL = @sSQL + 'NON'
END
SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)
END
ELSE
BEGIN
SET @sSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
BEGIN
SET @sSQL = @sSQL + 'UNIQUE '
END
-- Check if the index is clustered
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
BEGIN
SET @sSQL = @sSQL + 'CLUSTERED '
END
SELECT
@sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13),
@colCount = 0
END
-- Get the number of cols in the index
SELECT @colCount = COUNT(*)
FROM sys.index_columns ic
INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
WHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0
-- Get the file group info
SELECT @location = f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE o.object_id = @idxTableID AND i.index_id = @idxid
-- Get all columns of the index
INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )
SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column
FROM sys.index_columns ic
INNER JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
WHERE ic.object_id = @idxTableID AND index_id = @idxid
ORDER BY ic.index_column_id
IF @@ROWCOUNT > 0
BEGIN
SELECT @CurrentCol = 1
SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''
WHILE @CurrentCol <= @ColCount
BEGIN
SELECT @ColumnIDInTable = ColumnIDInTable,
@Name = Name,
@ColumnIDInIndex = ColumnIDInIndex,
@IsIncludedColumn = IsIncludedColumn
FROM #ColumnListing
WHERE [ColumnListingID] = @CurrentCol
IF @IsIncludedColumn = 0
BEGIN
SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '
-- Check the sort order of the index cols ????????
IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0
BEGIN
SET @sIndexCols = @sIndexCols + ' ASC '
END
ELSE
BEGIN
SET @sIndexCols = @sIndexCols + ' DESC '
END
IF @CurrentCol < @colCount
BEGIN
SET @sIndexCols = @sIndexCols + ', '
END
END
ELSE
BEGIN
-- Check for any include columns
IF LEN(@sIncludeCols) > 0
BEGIN
SET @sIncludeCols = @sIncludeCols + ','
END
SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'
END
SET @CurrentCol = @CurrentCol + 1
END
TRUNCATE TABLE #ColumnListing
--append to the result
IF LEN(@sIncludeCols) > 0
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '
ELSE
SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '
-- Build the options
SET @sParamSQL = 'WITH ( PAD_INDEX = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '
IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
SET @sParamSQL = @sParamSQL + 'ON,'
ELSE
SET @sParamSQL = @sParamSQL + 'OFF,'
-- Tweak this - identity = 100% - convert 0 to 100%
SET @sParamSQL = @sParamSQL + ' FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) ) + ', '
SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) '
SET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL
-- IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT
IF (@IsPrimaryKey = 0)
BEGIN
SET @sIndexCols = @sIndexCols + ' ON [' + @location + ']'
END
PRINT @sIndexCols + CHAR(13)
END
SET @CurrentIndex = @CurrentIndex + 1
END
END