Moving indexes

• san43

  Hall of Fame

  Points: 3196

  More actions

  October 29, 2007 at 12:24 pm

  #122060

  How can we move all the non clustered indexes in one database into a different file in a filegroup?

• Dinakar Nethi-176633

  SSCarpal Tunnel

  Points: 4689

  More actions

  October 29, 2007 at 12:59 pm

  #746372

  I think you have to drop the indexes and recreate them on the new filegroup.

  ******************
  Dinakar Nethi
  Life is short. Enjoy it.
  ******************

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  October 29, 2007 at 2:27 pm

  #746393

  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