moving large number of tables to different filegroups

Question

An existing database has a large number of tables stored in the PRIMARY filegroup. I want to move theses tables and their indexes on different filegroups automatically, depending on the "prefix" of the table name.

for example, with 5 tables named like this :

ABC_XXXX
ABC_YYYY
DEF_ZZZZ
DEF_TTTT
GHI_UUUU

All tables beginning by ABC should be moved to filegroup FG1, DEF to filegroup FG2 and other tables to filegroup DEFAULT.

This can be done using the command CREATE INDEX :

CREATE (UNIQUE|CLUSTERED|) INDEX <Index Name> ON <Table Name>(<Index Columns>)
       WITH (DROP_EXISTING = ON) ON <New Filegroup>

The biggest problem with this command is to retrieve the columns of each index in their proper order.

Answer 1

There are a lot of things you are leaving out of your description, which the script will need to accommodate for - is the index a primary key or a unique constraint? are any of the columns descending? is the index filtered? does it have any INCLUDE columns? While you can certainly generate a script manually, why not use the Generate Scripts wizard?

1. in Object Explorer, right-click your database
2. choose Tasks > Generate Scripts...
3. Click Next
4. Choose "Select specific database objects" and then select all the tables named ABC...
5. Click Next
6. Click Advanced
7. Scroll down and change "Script Indexes" to True
8. Click OK
9. Change the option to "Save to new query window"
10. Click Next / Next / Finish

Now you have a query window with all of your scripts, you'll have to remove some and do some hand-massaging of others, but for the indexes that aren't part of the PK you should simply be able to search and replace for DROP_EXISTING = OFF to DROP_EXISTING = ON and then swap out [PRIMARY] for FG1...

Here is a script that uses sys.indexes, sys.columns, and sys.index_columns - it creates a unique index in the case where an explicit primary key existed (since you haven't provided the "re-create PK with DROP_EXISTING" syntax I've asked for). This deals with include columns, fill factors, puts the columns in the correct order and even makes sure to build the unique/clustered indexes first.

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
  SELECT 
    obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
    + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
    i.[object_id],
    i.index_id,
    i.name,
    uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
    type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
    ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
    dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
        WHEN 'ABC' THEN 'FG1'
        WHEN 'DEF' THEN 'FG2'
        ELSE 'DEFAULT'
        END
  FROM sys.indexes AS i
  INNER JOIN sys.partitions AS p
  ON i.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE i.index_id > 0
  -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
  SELECT
    name = QUOTENAME(c.name),
    ic.key_ordinal,
    ic.[object_id],
    ic.index_id,
    sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
    ic.is_included_column
  FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.[object_id] = c.[object_id]
  AND ic.column_id = c.column_id
  WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10) 
    + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
    + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols 
        WHERE cols.object_id = src.object_id 
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 0
        ORDER BY cols.key_ordinal 
        FOR XML PATH('')), 1, 1, '') + ')' 
    + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols 
        WHERE cols.[object_id] = src.[object_id] 
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 1
        ORDER BY cols.key_ordinal 
        FOR XML PATH('')), 1, 1, '') + ')', '') 
    + ' WITH (DROP_EXISTING = ON' + ff
    + ') ON ' + dest + ';'
  FROM src
  ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;

For completeness, here is a script that adds sys.partitions and several SQL Server 2008-specific features such as filtered indexes and data compression.

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

;WITH src AS
(
  SELECT 
    obj = QUOTENAME(OBJECT_SCHEMA_NAME(i.[object_id]))
    + '.' + QUOTENAME(OBJECT_NAME(i.[object_id])),
    i.[object_id],
    i.index_id,
    i.name,
    uniq = CASE i.is_unique WHEN 1 THEN ' UNIQUE' ELSE '' END,
    type_desc = i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS,
    filter = CASE WHEN i.has_filter = 1 THEN ' WHERE ' + i.filter_definition ELSE '' END,
    ff = ', FILLFACTOR = ' + CONVERT(VARCHAR(3), i.fill_factor),
    dc = CASE p.data_compression_desc WHEN 'NONE' THEN '' 
        ELSE ', DATA_COMPRESSION = ' + p.data_compression_desc END,
    dest = CASE LEFT(OBJECT_NAME(i.[object_id]), 3)
        WHEN 'ABC' THEN 'FG1'
        WHEN 'DEF' THEN 'FG2'
        ELSE 'DEFAULT'
        END
  FROM sys.indexes AS i
  INNER JOIN sys.partitions AS p
  ON i.[object_id] = p.[object_id]
  AND i.index_id = p.index_id
  WHERE i.index_id > 0
  -- AND OBJECT_NAME(i.object_id) IN ('list','of','tables')
),
cols AS
(
  SELECT
    name = QUOTENAME(c.name),
    ic.key_ordinal,
    ic.[object_id],
    ic.index_id,
    sort = CASE ic.is_descending_key WHEN 1 THEN ' DESC' ELSE ' ' END,
    ic.is_included_column
  FROM sys.index_columns AS ic
  INNER JOIN sys.columns AS c
  ON ic.[object_id] = c.[object_id]
  AND ic.column_id = c.column_id
  WHERE ic.[object_id] IN (SELECT [object_id] FROM src)
)
SELECT @sql = @sql + CHAR(13) + CHAR(10) 
    + N'CREATE ' + uniq + ' ' + type_desc + ' INDEX ' + QUOTENAME(name)
    + ' ON ' + obj + '(' + STUFF((SELECT ',' + name + sort FROM cols 
        WHERE cols.object_id = src.object_id 
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 0
        ORDER BY cols.key_ordinal 
        FOR XML PATH('')), 1, 1, '') + ')' 
    + COALESCE(' INCLUDE(' + STUFF((SELECT ',' + name FROM cols 
        WHERE cols.[object_id] = src.[object_id] 
        AND cols.index_id = src.index_id
        AND cols.is_included_column = 1
        ORDER BY cols.key_ordinal 
        FOR XML PATH('')), 1, 1, '') + ')', '') 
    + filter + ' WITH (DROP_EXISTING = ON' + ff + dc
    + ') ON ' + dest + ';'
  FROM src
  ORDER BY uniq DESC, type_desc;

SELECT @sql;
-- EXEC sp_executesql @sql;

Answer 2

Had the same problem and this is the script I came up with (tested, and works just as you would expect):

DECLARE @Target_Filegroup sysname = N'XXX';
-----------------------------------------------------------------------------------------
;WITH [IX] AS(
SELECT
[Schema]                 = SCHEMA_NAME(so.[schema_id]) COLLATE DATABASE_DEFAULT,
[Object_Name]            = so.[name] COLLATE DATABASE_DEFAULT,
[Object_Type]            = so.[type],
[Is_Published]           = so.[is_published],
[Is_Schema_Published]    = so.[is_schema_published],
[IX_Name]                = ix.[name] COLLATE DATABASE_DEFAULT,
[IX_Type]                = ix.[type],
[IX_Type_Desc]           = ix.[type_desc] COLLATE DATABASE_DEFAULT,
[Is_PK]                  = ix.[is_primary_key],
[Is_Unique]              = ix.[is_unique],
[IX_Data_Space]          = ds.[name] COLLATE DATABASE_DEFAULT,
[Is_UC]                  = ix.[is_unique_constraint],
[FF]                     = ix.[fill_factor],
[Is_Padded]              = ix.[is_padded],
[Is_Disabled]            = ix.[is_disabled],
[Is_Hypothetical]        = ix.[is_hypothetical],
[Allow_Row_Locks]        = ix.[allow_row_locks],
[Allow_Page_Locks]       = ix.[allow_page_locks],
[Has_Filter]             = ix.[has_filter],
[Filter]                 = ix.[filter_definition] COLLATE DATABASE_DEFAULT,
--[auto_created]         = ix.[auto_created],
--[optimize_seq_key]     = ix.[optimize_for_sequential_key],
[Indexed_Columns] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])))
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 0
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT,
[Indexed_Columns_Order] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])), CASE [is_descending_key] WHEN 1 THEN ' DESC' ELSE ' ASC' END)
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 0
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT,
[Included_Columns] = STUFF(( SELECT [text()] = CONCAT(', ', QUOTENAME(COL_NAME(ic.[object_id],ic.[column_id])))
                            FROM sys.index_columns ic
                            WHERE ic.[object_id] = so.[object_id]
                            AND ic.[index_id] = ix.[index_id]
                            AND ic.[is_included_column] = 1
                            ORDER BY ic.[key_ordinal]
                            FOR XML PATH('')
                            ), 1, 2, '') COLLATE DATABASE_DEFAULT
FROM sys.objects so
LEFT JOIN sys.indexes ix ON so.[object_id] = ix.[object_id]
LEFT JOIN sys.data_spaces ds ON ix.[data_space_id] = ds.[data_space_id]
WHERE so.[type] IN ('U', 'V')
AND so.[is_ms_shipped] = 0
AND ix.[type] IS NOT NULL --| so we get heaps, and indexed views 
)
SELECT
[Schema], [Object_Name], [Object_Type],
--[Is_Published], [Is_Schema_Published],
[IX_Name],
[IX_Data_Space],
[IX_Move_SQL] = CASE WHEN [IX_Data_Space] <> @Target_Filegroup AND [IX_Type] IN (1,2) THEN CONCAT(
                                                'CREATE ', CASE [Is_Unique] WHEN 1 THEN 'UNIQUE ' END, [IX_Type_Desc], ' INDEX ', QUOTENAME([IX_Name]),
                                                ' ON ', QUOTENAME([Schema]), '.', QUOTENAME([Object_Name]), ' (', [Indexed_Columns_Order], ')',
                                                CASE WHEN [Included_Columns] IS NOT NULL THEN CONCAT(' INCLUDE (', [Included_Columns], ')') END,
                                                CASE WHEN [Has_Filter] = 1 THEN CONCAT(' WHERE ', [Filter]) END,
                                                ' WITH (PAD_INDEX=', CASE [Is_Padded] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', FILLFACTOR=', CASE WHEN [FF] = 0 THEN '100' ELSE CAST([FF] as varchar(3)) COLLATE DATABASE_DEFAULT END,
                                                ', ALLOW_ROW_LOCKS=', CASE [Allow_Row_Locks] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', ALLOW_PAGE_LOCKS=', CASE [Allow_Page_Locks] WHEN 1 THEN 'ON' ELSE 'OFF' END,
                                                ', DROP_EXISTING=ON ',')',
                                                ' ON ', QUOTENAME(@Target_Filegroup), ';')

                                                 END COLLATE DATABASE_DEFAULT
FROM [IX]
ORDER BY [Object_Type] ASC, [Schema] ASC , [Object_Name] ASC;