October 29, 2007 at 12:24 pm
How can we move all the non clustered indexes in one database into a different file in a filegroup?
October 29, 2007 at 12:59 pm
I think you have to drop the indexes and recreate them on the new filegroup.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
October 29, 2007 at 2:27 pm
There's a GUI indicator that you can change the filegroup (look on the Storage page). However, when I scripted one out it just gave me a generic create index on filegroup statement.
So, yeah, you can create a script to drop and recreate all your indexes via sys.indexes and sys.objects but that's about it. I just did something similar (this was just a drop). Here's the query I used:
select
'DROP INDEX '+QUOTENAME(si.name)+' ON '+QUOTENAME(object_name(si.object_id)),
object_name(si.object_id) as TheTable,
si.*
from
sys.indexes si
INNER JOIN
sys.objects so
ON
so.object_id = si.object_id
where
so.create_date < 'August 29, 2007'
and
si.type = 2 --non-clustered
and
si.is_primary_key = 0 --not a PK (same issue as unique)
and
si.is_unique_constraint = 0 --drop index won't work if it's a constraint
and
object_name(si.object_id) like ('fact_imp_%') --limit to certain tables
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply