Scripting CREATE INDEX commands to a new filegroup in Visual C#.NET

Drawer USS Code 128 in Visual C#.NET Scripting CREATE INDEX commands to a new filegroup

EXEC usp_Manage_NCIX_FileGroup N'Script', N'NCIX_FG2' CREATE PROCEDURE [dbo].[usp_Manage_NCIX_FileGroup] @ScriptOnlyOrExecute NVARCHAR(6) /* N'Script' or N'Exec' */ , @NCIXFGName NVARCHAR(255) /* the name new filegroup to move NCIXs into*/ AS SET NOCOUNT ON /*cursor variables*/ DECLARE @tnm NVARCHAR(128), @ixnm NVARCHAR(128), @cnm NVARCHAR(128) , @schnm NVARCHAR(128), @isPK BIT, @isUn BIT, @isIncCol BIT , @cixnm NVARCHAR(128), @ctbschnm NVARCHAR(256) , @ixcr NVARCHAR(4000), @ixcnt INT, @indid INT, @order NVARCHAR(5) , @inccols NVARCHAR(4000) SELECT @ixcnt = 0, @cixnm = N'', @ctbschnm = N'' /*open cursor over schema / table / index / columns*/ DECLARE cr CURSOR FOR SELECT ss.name, so.name, si.name, N'[' + sc.name + N']', is_primary_key , CASE WHEN is_descending_key = 0 THEN N'' ELSE N' DESC' END , is_included_column, si.index_id, is_unique FROM sys.schemas ss JOIN sys.objects so on ss.schema_id = so.schema_id JOIN sys.indexes si on so.object_id = si.object_id JOIN sys.index_columns ic ON si.object_id = ic.object_id AND si.index_id = ic.index_id JOIN sys.columns sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id WHERE OBJECTPROPERTY (so.object_id, 'IsUserTable') = 1 AND si.index_id > 1 AND si.index_id < 255 /*only interested in NCIXs*/ ORDER BY ss.name, so.name, si.name, ic.index_column_id /*order crucial for cursor logic*/

FETCH NEXT FROM cr INTO @schnm, @tnm, @ixnm, @cnm, @isPK, @order, @isIncCol, @indid, @isUn /*move over cursor, constructing & executing a drop / create index statement per index*/ WHILE @@FETCH_STATUS = 0 BEGIN IF @ixnm != @cixnm or (@schnm+@tnm != @ctbschnm) /*new index or table*/ BEGIN /*if index, table or schema name changes, reinitialise*/ IF @schnm+@tnm != @ctbschnm SET @ctbschnm = @schnm+@tnm SELECT @ixcnt = @ixcnt + 1 IF @ixcnt > 1 BEGIN SELECT @ixcr = LEFT(@ixcr, LEN(@ixcr) - 2) + N')' + CASE WHEN LEN(@inccols) > 1 THEN N' INCLUDE (' + LEFT(@inccols, LEN(@inccols) - 2) + N')' ELSE N'' END + N' WITH (DROP_EXISTING = ON' + CASE WHEN SERVERPROPERTY('EngineEdition') = 3 THEN N', ONLINE = ON)' ELSE N')' END + N' ON [' + @NCIXFGName + ']' /*execution of create NCIX in other FG occurs on first row of NEXT index*/ PRINT @ixcr; IF @ScriptOnlyOrExecute = N'Exec' EXEC(@ixcr) END SELECT @cixnm = @ixnm, @inccols = '' SET @ixcr = N'create ' + CASE WHEN @isUn = 1 THEN N'unique ' ELSE N'' END + N'nonclustered index [' + @ixnm + N'] on [' + @schnm + N'].[' + @tnm + N'] (' + @cnm + @order + N', ' END ELSE BEGIN /*if same index, build key of included cols csv list*/ IF @isIncCol != 0 SET @inccols = @inccols + @cnm + N', ' ELSE SET @ixcr = @ixcr + @cnm + @order + N', ' END FETCH NEXT FROM cr INTO @schnm, @tnm, @ixnm, @cnm, @isPK, @order, @isIncCol, @indid, @isUn END /*should usually be one last index (assuming there were any)*/ IF @ixcnt > 1 BEGIN SELECT @ixcr = LEFT(@ixcr, LEN(@ixcr) - 2) + N')' + CASE WHEN LEN(@inccols) > 1 THEN N' INCLUDE (' + LEFT(@inccols, LEN(@inccols) - 2) + N')' ELSE N'' END + N' WITH (DROP_EXISTING = ON' + CASE WHEN SERVERPROPERTY('EngineEdition') = 3 THEN N', ONLINE = ON)' ELSE N')' END + N' ON [' + @NCIXFGName + ']'

Note the following points about the preceding script: The script iterates over all schemas and their user tables within the current database, identifying all nonclustered indexes and generating a CREATE INDEX .. WITH DROP_EXISTING ON [NCIX_FG] script for each index, which is then executed dynamically to move each nonclustered index. The script will not attempt to move tables. Heaps or clustered indexes are eliminated by the cursor query s WHERE filter:

and si.index_id > 1 and si.index_id < 255"

Unique definitions, key order, and included columns are all also preserved, so nonclustered indexes should be moved identically with their current definitions. Each individual CREATE INDEX statement is also printed, allowing easy review of the commands that have been dynamically executed by the script.