CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Jānis
Jānis

Reputation: 2266

Move indexes to other filegroup

Is there a nice script, that generates script to move clustered and non-clustered indexes (+ heaps) to other file group?

In perfect world it would look like procedure, where input is table name, new file group name as string (so can give also partitioning schema with columns too) and optional index name (if none given, then moves all indexes or heap). The script would output result (script) in message panel in SSMS, so can review before running.. It would also take in account the enterprise version features (ONLINE) and would make nice scripts for clustered indexes, that can not be dropped and re-created, like here:

CREATE unique clustered INDEX PK_TableName
On dbo.TableName
(
    [ColumnName] ASC
)
WITH (DROP_EXISTING=ON, ONLINE=ON)
ON FileGroup

It would also take in account included columns, filtered indexes.

Upvotes: 0

Views: 2124

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

See

https://dba.stackexchange.com/questions/16708/moving-large-number-of-tables-to-different-filegroups

and

Generate CREATE scripts for a list of indexes

You just need to massage the output a little bit to include the new filegroup designation and the "drop existing" clause...

Upvotes: 3

Related Questions