October 13, 2008 at 3:02 pm
Can some one please provide me the script or method to move non-clustered indexes (database level)from secondary file to primary file. I moved the indexes to secondary file by creating the filegroup, now I want to use the reverse method to move these indexes back to primary file with clustered data. Please help.
October 13, 2008 at 3:12 pm
Here's a script that will create the script to move your nonclustered indexes back to Primary, at least for simple ones that only have one column. You may have to tweak the results of this before running unfortunately if your index has multiple columns and/or include columns, but this at least gets you most of the way there:
SELECT 'CREATE INDEX ' + i.name + ' ON ' + s.name + '.' + o.name + ' (' + c.name + ') WITH (DROP_EXISTING=ON) ON "PRIMARY"'
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE o.type = 'U'
AND i.is_primary_key = 0
AND i.type_desc <> 'CLUSTERED'
AND i.is_unique_constraint = 0
AND ds.name <> 'PRIMARY'
ORDER BY s.name, o.name
October 13, 2008 at 6:04 pm
Wait a minute. That script doesn't work correctly if there is more than one field in the index.
Try this.
SELECT CREATE_INDEX_SQL
FROM (
SELECT TBL_NAME = o.name,
INDEX_NAME = i.name,
CREATE_INDEX_SQL = 'CREATE '
+ CASE i.type_desc WHEN 'CLUSTERED' THEN 'CLUSTERED' ELSE '' END
+ ' INDEX ' + i.name
+ ' ON ' + s.name + '.' + o.name + ' ('
+ (STUFF(( SELECT ', ' + sc.name
FROM sys.indexes si
JOIN sys.tables st
ON si.object_id = st.object_id
JOIN sys.index_columns sic
ON st.object_id = sic.object_id
AND si.index_id = sic.index_id
JOIN sys.columns sc
ON st.object_id = sc.object_id
AND sc.column_id = sic.column_id
WHERE si.name = i.name
--AND st.name = i.name
ORDER BY sic.index_column_id
FOR XML PATH(''))
, 1,2,''))
+ ')'
FROM sys.indexes i
INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
WHERE o.type = 'U'
AND i.is_primary_key = 0
AND i.type_desc <> 'CLUSTERED'
AND i.is_unique_constraint = 0
AND ds.name <> 'PRIMARY'
) o
GROUP BY CREATE_INDEX_SQL, TBL_NAME, INDEX_NAME
ORDER BY TBL_NAME, INDEX_NAME
muttering under my breath... now to go modify my utility scripts to use this rather than the old nested cursor... grumble
Gary Johnson
Sr Database Engineer
October 14, 2008 at 8:06 am
Thanks for the quick reply. I will test the script and let you know if I encountered any problems or if I need any further help. Thanks again guys!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply