Sunday, March 11, 2012

Dynamically Drop and re-create all indexes for all tables

Hi There

I have a database where i want to move all data and indexes to separate drives via filegroups.

Apparantly there is no better way to do this in 2005 than in 2000, which means i need to drop and re-created clustered and non clustered index accordingly on the new filegroups.

What i did in 2000 was loop through tables and indexes dropping the indexes and dynamically re-creating the index defintions from sysindexes etc.

Is there an easier way to do this is 2005, in a nutshell how would i dynamically script the create index statements for all indexes for all tables in 2005.

Thanx

If you need just rebuild indexes, you could use ALTER with REBUILD instead of DELETE and CREATE|||Implementing partitions would allow you to shift things around as desired.|||Hi Konstantin i do not see the ON FILEGROUP syntax in BOL with ALTER INDEX ?

No comments:

Post a Comment