HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

moving large number of tables to different filegroups

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sql-server-2008sqlsql-serverstackoverflowsql-server-2005dba
tablesnumberdifferentlargemovingfilegroups

Problem

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  ON ()
       WITH (DROP_EXISTING = ON) ON


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

Solution

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?

  • in Object Explorer, right-click your database



  • choose Tasks > Generate Scripts...



  • Click Next



  • Choose "Select specific database objects" and then select all the tables named ABC...



  • Click Next



  • Click Advanced



  • Scroll down and change "Script Indexes" to True



  • Click OK



  • Change the option to "Save to new query window"



  • 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)
)
SE

Code Snippets

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;
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;

Context

StackExchange Database Administrators Q#16708, answer score: 12

Revisions (0)

No revisions yet.