All the indexes and primary keys were created in primary file(.mdf). Is there any way to move all the index and primary key to secondary filegroup(.ndf)??
Use Following query:
DECLARE @SchemaName NVARCHAR(300),
@ObjectName NVARCHAR(300),
@IndexName NVARCHAR(300),
@Columns NVARCHAR(MAX),
@IncludeColumns NVARCHAR(MAX),
@Command NVARCHAR(MAX)
DECLARE IndexCursor CURSOR FOR
SELECT SCHEMA_NAME(o.schema_id),OBJECT_NAME(o.object_id), i.name,
STUFF((SELECT ',['+c.name+']'
FROM sys.index_columns ic
INNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column=0
ORDER BY ic.key_ordinal
FOR XML PATH('')
),1,1,'') IndexColumn,
STUFF((SELECT ',['+c.name+']'
FROM sys.index_columns ic
INNER JOIN sys.columns c ON c.column_id = ic.column_id AND c.object_id = ic.object_id
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.is_included_column=1
ORDER BY ic.key_ordinal
FOR XML PATH('')
),1,1,'') IncludeColumn,
i.filter_definition
FROM sys.indexes i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.data_spaces ds ON ds.data_space_id = i.data_space_id
WHERE ds.name = 'PRIMARY'
AND OBJECTPROPERTY(i.object_id,'IsUserTable')=1
OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @SchemaName, @ObjectName, @IndexName, @Columns, @IncludeColumns
WHILE @@FETCH_STATUS=0 BEGIN
SET @Command = 'ALTER INDEX ' + @IndexName + ' ON [' + @SchemaName + '].['+@ObjectName+']('+@Columns+') '+ ISNULL('Include ('+@IncludeColumns+')','') + ' ON [YourFileGroupName]'
EXEC(@Command)
FETCH NEXT FROM IndexCursor INTO @SchemaName, @ObjectName, @IndexName, @Columns, @IncludeColumns
END
CLOSE IndexCursor
DEALLOCATE IndexCursor