SQL Server – Moving Large Number of Tables to Different Filegroups

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;