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)
)
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;
Best Answer
For me, this is a question of simplicity-of-design vs performance-and-scalability.
If I am designing a non-critical database that won't see a huge amount of traffic, then I typically would go with a single file per database since it is easy to configure and maintain.
However, if your recovery point objective is to be able to recover the database to within several minutes of any failure, and your recovery time objectives are to be able to recover within as short a time as possible, I would carefully plan the filegroups and associated files as:
If there is a requirement to store many file-based pieces of data, for instance images or documents, with relationships to data stored in SQL Server, I would use
FILESTREAM, which requires its own filegroup.