scripts to move indexes to diffrnt file groups

  • need some assistance regarding scripts...The requirement as follows

    -------------------------

    Move all non-clustered indexes to INDEX file group

    Move all user (U) type tables and clustered indexes to DATA file group

    For the above,we have 4 schemas and 150+ tables in the database,can we move all indexex/tables at a time using scripts

  • Have you checked the script library here or a google search? There should be something to get you started. It's not trivial though.

    One question though. Why are you doing this? What's the reason behind moving nonclustered indexes to another filegroup.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Checked in script library,but not found the script

  • You can either drop the existing index and create the new one on the new file group or use WITH (DROP_EXISTING = ON), instead of explicity dropping it first.

  • janardhan4891 (12/23/2011)


    Checked in script library,but not found the script

    Have another look. I remember seeing a couple of these scripts in there.

    Just curious, and like Gail asked, why are you looking to make these moves?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply