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
Your example should indeed work but it's not the recommended approach. By doing a drop/create on the CL followed by a drop/create in the NC, you're rebuilding the NC indexes 3 times.
Despite your approach not being the fastest way to do this, it should work. Can't explain what's going wrong but can suggest a different method. Can you try the following:
Run the following to drop the PK/CL and rebuild on the new FG
ALTER TABLE [lntmuser].[matter] DROP CONSTRAINT [matter_k__2] GO
CREATE UNIQUE CLUSTERED INDEX CLIX_matter_sysid ON [lntmuser].[matter] (sysid ASC) -- Add appropriate fill factor option etc ON [Alternate] GO
ALTER TABLE [lntmuser].[matter] ADD CONSTRAINT PK_Matter PRIMARY KEY (sysid) GO
Create the NC indexes
If that works, we can go back to working out why your first script failed.
Edit: Just in case... you mentioned checking reads for the new filegroup. What are you using to check this? Are you looking at sys.dm_io_virtual_file_stats and seeing no change as the tables are fully cached in the buffer pool?