Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
You should be able to adapt my answer to stackoverflow.com/questions/9626892/…, in which I query the system views to discover all foreign keys, and turn that into a dynamic query to create a massive select statement. –  Griffin Apr 17 '12 at 16:39
add comment

migrated from stackoverflow.com Apr 18 '12 at 17:26

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 5 down vote accepted

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 = ', FILL_FACTOR = ' + 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 = ', FILL_FACTOR = ' + 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;
share|improve this answer
    
there should be a way to do this by script only using sys.indexes and sys.index_columns, I'm working on it –  psadac Apr 17 '12 at 19:19
    
Well of course there is. Did you search? stackoverflow.com/questions/1121522/… –  Aaron Bertrand Apr 17 '12 at 19:30
    
And of course the problem is that with sys.indexes and sys.index_columns alone, you're not necessarily going to get all of the properties for the index, for example DATA_COMPRESSION. –  Aaron Bertrand Apr 17 '12 at 19:45
    
Also how do you plan to move indexes that are enforcing a primary key constraint? AFAIK you can't re-create a PK with DROP_EXISTING unless when you re-create it you convert it to a unique index. –  Aaron Bertrand Apr 17 '12 at 19:57
    
for now all databases are on a sql server 2005 server, so DATA_COMPRESSION is not a problem. –  psadac Apr 17 '12 at 20:01
show 12 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.