Missing the filegroup clause so adding it, adding GO clause and modifying the cursor query:
-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID,
FG.name
FROM sys.indexes SI
left outer join sys.filegroups FG
on SI.data_space_id = FG.data_space_id
WHERE OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
and SI.Name is not null
and SI.is_primary_key = 0 and SI.is_unique_constraint = 0
AND INDEXPROPERTY(SI.Object_ID, SI.Name, 'IsStatistics') = 0
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID
DECLARE @IxTable sysname
DECLARE @IxTableID INT
DECLARE @IxName sysname
DECLARE @FGName sysname
DECLARE @IxID INT
-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FGName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000)
SET @IXSQL = 'CREATE '
-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '
SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('
-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR
SELECT SC.Name
FROM sys.index_columns IC
JOIN sys.columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.key_ordinal
DECLARE @IxColumn sysname
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1
-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '
SET @IXSQL = @IXSQL + @IxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn
SET @IXSQL = @IXSQL + ')' + 'on '+ @FGName
-- Print out the CREATE statement for the index
PRINT @IXSQL
PRINT 'GO'
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FGName
END
CLOSE cIX
DEALLOCATE cIX