-
-
Save dcs619/cd0a7682c4a33b06dc3fb9891e7f401e to your computer and use it in GitHub Desktop.
A T-SQL stored procedure for moving an index from one file group to another. The original script was found at http://blogs.msdn.com/b/ramoji/archive/2008/09/26/how-to-move-existing-indexes-from-one-filegroup-to-another.aspx and updated according to responses found on the same page.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| -- See below for moving a non-clustered file index. Moving a clustered index is the same | |
| -- as moving the data, as each row lives as a leaf somewhere in the index tree. | |
| -- Current syntax for moving a CLUSTERED file index: | |
| PRINT 'Moving PK_dbo.Group index' | |
| CREATE UNIQUE CLUSTERED INDEX | |
| [PK_dbo.Group] | |
| ON | |
| [Group] ( [Id] ) | |
| WITH ( | |
| DROP_EXISTING = ON, | |
| PAD_INDEX = OFF, | |
| STATISTICS_NORECOMPUTE = OFF, | |
| SORT_IN_TEMPDB = ON, | |
| IGNORE_DUP_KEY = OFF, | |
| ONLINE = OFF, | |
| ALLOW_ROW_LOCKS = ON, | |
| ALLOW_PAGE_LOCKS = ON | |
| ) ON [SECONDARY] | |
| GO | |
| */ | |
| -- The following script generates index moves for clustered/non-clustered indexes as needed | |
| ;WITH CTE AS ( | |
| select ic.index_id + ic.object_id AS indexId | |
| , t.name AS tableName | |
| , i.name AS indexName | |
| , c.name AS columnName | |
| , i.type_desc COLLATE DATABASE_DEFAULT as indexType | |
| , ic.is_included_column as columnIncludes | |
| , ic.is_descending_key as is_descending | |
| , i.is_unique | |
| from sys.indexes i | |
| inner join sys.index_columns ic | |
| on i.index_id = ic.index_id | |
| and i.object_id = ic.object_id | |
| --and i.type_desc = 'NONCLUSTERED' | |
| inner join sys.columns c | |
| on ic.column_id = c.column_id | |
| and i.object_id = c.object_id | |
| inner join sys.tables t | |
| on i.object_id = t.object_id | |
| ) | |
| select N' | |
| PRINT ''Moving ' + indexName + ' index on ' + tableName + ''' | |
| CREATE' + case is_unique when 1 then ' UNIQUE' else '' end + ' ' + c.indexType + ' INDEX | |
| [' + indexName + '] | |
| ON | |
| [' + tableName + '] (' + | |
| STUFF( ( select ', [' + a.columnName + ']' + case is_descending when 1 then ' DESC' else '' end | |
| from CTE a where c.IndexId = a.IndexId and columnIncludes = 0 FOR XML PATH('')), 1 , 1, '') | |
| + ' ) ' + | |
| ISNULL(CHAR(10) + ' INCLUDE (' + STUFF( ( select ', [' + b.columnName + ']' from CTE b where c.IndexId = b.IndexId and columnIncludes = 1 FOR XML PATH('')), 1 , 1, '') + ' ) ' + CHAR(10), CHAR(10)) + | |
| 'WITH ( | |
| DROP_EXISTING = ON, | |
| PAD_INDEX = OFF, | |
| STATISTICS_NORECOMPUTE = OFF, | |
| SORT_IN_TEMPDB = ON, | |
| IGNORE_DUP_KEY = OFF, | |
| ONLINE = OFF, | |
| ALLOW_ROW_LOCKS = ON, | |
| ALLOW_PAGE_LOCKS = ON | |
| ) ON [SECONDARY] | |
| GO' | |
| from CTE c | |
| group by indexId, c.tableName, indexName, indexType, is_unique | |
| order by c.tableName | |
| /* | |
| IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MoveIndexToFileGroup]') AND type in (N'P', N'PC')) | |
| BEGIN | |
| DROP PROCEDURE [dbo].[MoveIndexToFileGroup] | |
| END | |
| GO | |
| CREATE PROC [dbo].[MoveIndexToFileGroup] ( | |
| @DBName sysname, | |
| @SchemaName sysname = 'dbo', | |
| @ObjectNameList Varchar(Max), | |
| @IndexName sysname = null, | |
| @FileGroupName varchar(100) | |
| ) WITH RECOMPILE | |
| AS | |
| BEGIN | |
| SET NOCOUNT ON; | |
| DECLARE @IndexSQL NVarchar(Max) | |
| DECLARE @IndexKeySQL NVarchar(Max) | |
| DECLARE @IncludeColSQL NVarchar(Max) | |
| DECLARE @FinalSQL NVarchar(Max) | |
| DECLARE @CurLoopCount Int | |
| DECLARE @MaxLoopCount Int | |
| DECLARE @StartPos Int | |
| DECLARE @EndPos Int | |
| DECLARE @ObjectName sysname | |
| DECLARE @IndName sysname | |
| DECLARE @IsUnique Varchar(10) | |
| DECLARE @Type Varchar(25) | |
| DECLARE @IsPadded Varchar(5) | |
| DECLARE @IgnoreDupKey Varchar(5) | |
| DECLARE @AllowRowLocks Varchar(5) | |
| DECLARE @AllowPageLocks Varchar(5) | |
| DECLARE @FillFactor Int | |
| DECLARE @ExistingFGName Varchar(Max) | |
| DECLARE @FilterDef NVarchar(Max) | |
| DECLARE @ErrorMessage NVARCHAR(4000) | |
| DECLARE @SQL nvarchar(4000) | |
| DECLARE @RetVal Bit | |
| DECLARE @ObjectList Table(Id Int Identity(1,1),ObjectName sysname) | |
| DECLARE @WholeIndexData TABLE ( | |
| ObjectName SYSNAME | |
| ,IndexName SYSNAME | |
| ,Is_Unique BIT | |
| ,Type_Desc VARCHAR(25) | |
| ,Is_Padded BIT | |
| ,[Ignore_Dup_Key] BIT | |
| ,[Allow_Row_Locks] BIT | |
| ,[Allow_Page_Locks] BIT | |
| ,Fill_Factor INT | |
| ,Is_Descending_Key BIT | |
| ,ColumnName SYSNAME | |
| ,Is_Included_Column BIT | |
| ,FileGroupName VARCHAR(MAX) | |
| ,Has_Filter BIT | |
| ,Filter_Definition NVARCHAR(MAX) | |
| ,key_ordinal TINYINT | |
| ) | |
| DECLARE @DistinctIndexData TABLE ( | |
| Id INT IDENTITY(1, 1) | |
| ,ObjectName SYSNAME | |
| ,IndexName SYSNAME | |
| ,Is_Unique BIT | |
| ,Type_Desc VARCHAR(25) | |
| ,Is_Padded BIT | |
| ,[Ignore_Dup_Key] BIT | |
| ,[Allow_Row_Locks] BIT | |
| ,[Allow_Page_Locks] BIT | |
| ,Fill_Factor INT | |
| ,FileGroupName VARCHAR(Max) | |
| ,Has_Filter BIT | |
| ,Filter_Definition NVARCHAR(Max) | |
| ) | |
| -------------Validate arguments---------------------- | |
| IF(@DBName IS NULL) | |
| BEGIN | |
| SELECT @ErrorMessage = 'Database Name must be supplied.' | |
| GOTO ABEND | |
| END | |
| IF(@ObjectNameList IS NULL) | |
| BEGIN | |
| SELECT @ErrorMessage = 'Table or View Name(s) must be supplied.' | |
| GOTO ABEND | |
| END | |
| IF(@FileGroupName IS NULL) | |
| BEGIN | |
| SELECT @ErrorMessage = 'FileGroup Name must be supplied.' | |
| GOTO ABEND | |
| END | |
| --Check for the existence of the Database | |
| IF NOT EXISTS(SELECT Name FROM sys.databases where Name = @DBName) | |
| BEGIN | |
| SET @ErrorMessage = 'The specified Database does not exist' | |
| GOTO ABEND | |
| END | |
| --Check for the existence of the Schema | |
| IF (upper(@SchemaName) <> 'DBO') | |
| BEGIN | |
| SET @SQL = 'SELECT @RetVal = COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.schemas WHERE name = ''' + @SchemaName + '''' | |
| BEGIN TRY | |
| EXEC sp_executesql @SQL, N'@RetVal Bit OUTPUT', @RetVal OUTPUT | |
| END TRY | |
| BEGIN CATCH | |
| SELECT @ErrorMessage = ERROR_MESSAGE() | |
| GOTO ABEND | |
| END CATCH | |
| IF (@RetVal = 0) | |
| BEGIN | |
| SELECT @ErrorMessage = 'No Schema with the name ' + @SchemaName + ' exists in the Database ' + @DBName | |
| GOTO ABEND | |
| END | |
| END | |
| --CHECK FOR THE EXISTENCE OF THE FILEGROUP | |
| SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.filegroups WHERE name = ''' + @FileGroupName + '''' | |
| BEGIN TRY | |
| EXEC sp_executesql @SQL,N'@RetVal Bit OUTPUT',@RetVal OUTPUT | |
| END TRY | |
| BEGIN CATCH | |
| SELECT @ErrorMessage = ERROR_MESSAGE() | |
| GOTO ABEND | |
| END CATCH | |
| IF(@RetVal = 0) | |
| BEGIN | |
| SELECT @ErrorMessage = 'No FileGroup with the name ' + @FileGroupName + ' exists in the Database ' + @DBName | |
| GOTO ABEND | |
| END | |
| ----------Get the objects from the concatenated list---------------------------------------------------- | |
| SET @StartPos = 0 | |
| SET @EndPos = 0 | |
| WHILE(@EndPos >= 0) | |
| BEGIN | |
| SELECT @EndPos = CHARINDEX(',',@ObjectNameList,@StartPos) | |
| IF(@EndPos = 0) --Means, separator is not found | |
| BEGIN | |
| INSERT INTO @ObjectList | |
| SELECT SUBSTRING(@ObjectNameList,@StartPos,(LEN(@ObjectNameList) - @StartPos)+1) | |
| BREAK | |
| END | |
| INSERT INTO @ObjectList | |
| SELECT SUBSTRING(@ObjectNameList,@StartPos,(@EndPos - @StartPos)) | |
| SET @StartPos = @EndPos + 1 | |
| END | |
| -------------Check for the validity of all the Objects---------------------- | |
| SET @StartPos = 1 | |
| SELECT @EndPos = COUNT(*) FROM @ObjectList | |
| WHILE(@StartPos <= @EndPos) | |
| BEGIN | |
| SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos | |
| --CHECK FOR EXISTENCE OF THE OBJECT | |
| SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Objects WHERE type IN (''U'',''V'') AND name = ''' + @ObjectName + '''' | |
| BEGIN TRY | |
| EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT | |
| END TRY | |
| BEGIN CATCH | |
| SELECT @ErrorMessage = ERROR_MESSAGE() | |
| GOTO ABEND | |
| END CATCH | |
| IF(@RetVal = 0) | |
| BEGIN | |
| SELECT @ErrorMessage = 'No Table or View with the name ' + @ObjectName + ' exists in the Database ' + @DBName | |
| GOTO ABEND | |
| END | |
| --Check for existence of Index | |
| IF(@IndexName IS NOT NULL) | |
| BEGIN | |
| SET @SQL = 'SELECT @RetVal=COUNT(*) FROM ' + QUOTENAME(@DBName) + '.sys.Indexes si INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Objects so ' | |
| SET @SQL = @SQL + ' ON si.Object_Id = so.Object_Id WHERE so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25)) | |
| SET @SQL = @SQL + ' AND so.name = ''' + @ObjectName + ''' AND si.name = ''' + @IndexName + '''' | |
| BEGIN TRY | |
| EXEC sp_executesql @SQL,N'@RetVal Int OUTPUT',@RetVal OUTPUT | |
| END TRY | |
| BEGIN CATCH | |
| SELECT @ErrorMessage = ERROR_MESSAGE() | |
| GOTO ABEND | |
| END CATCH | |
| IF(@RetVal = 0) | |
| BEGIN | |
| SELECT @ErrorMessage = 'No Index with the name ' + @IndexName + ' exists on the Object ' + @ObjectName | |
| GOTO ABEND | |
| END | |
| END | |
| SET @StartPos = @StartPos + 1 | |
| END | |
| -------------Loop till all the Objects are processed---------------------- | |
| SET @StartPos = 1 | |
| SELECT @EndPos = COUNT(*) FROM @ObjectList | |
| WHILE(@StartPos <= @EndPos) | |
| BEGIN | |
| SELECT @ObjectName = ObjectName FROM @ObjectList WHERE Id = @StartPos | |
| -------------Build the SQL to get the index data based on the inputs provided---------------------- | |
| SET @IndexSQL = | |
| 'SELECT so.Name as ObjectName, si.Name as IndexName,si.Is_Unique,si.Type_Desc' | |
| + ',si.Is_Padded,si.Ignore_Dup_Key,si.Allow_Row_Locks,si.Allow_Page_Locks,si.Fill_Factor,sic.Is_Descending_Key' | |
| + ',sc.Name as ColumnName,sic.Is_Included_Column,sf.Name as FileGroupName,'+ CASE WHEN @@VERSION LIKE '%Server 2005%' THEN '0 as Has_Filter, N'''' as Filter_Definition' ELSE 'si.Has_Filter,si.Filter_Definition' END +',sic.Key_Ordinal FROM ' | |
| + QUOTENAME(@DBName) + '.sys.Objects so INNER JOIN ' + QUOTENAME(@DBName) + '.sys.Indexes si ON so.Object_Id = si.Object_id INNER JOIN ' | |
| + QUOTENAME(@DBName) + '.sys.FileGroups sf ON sf.Data_Space_Id = si.Data_Space_Id INNER JOIN ' | |
| + QUOTENAME(@DBName) + '.sys.Index_columns sic ON si.Object_Id = sic.Object_Id AND si.Index_id = sic.Index_id INNER JOIN ' | |
| + QUOTENAME(@DBName) + '.sys.Columns sc ON sic.Column_Id = sc.Column_Id and sc.Object_Id = sic.Object_Id ' | |
| + ' WHERE so.Name = ''' + @ObjectName + '''' | |
| + ' AND so.Schema_id = ' + CAST(Schema_Id(@Schemaname) as varchar(25)) + ' AND si.Type_Desc = ''NONCLUSTERED'' ' | |
| IF(@IndexName IS NOT NULL) | |
| BEGIN | |
| SET @IndexSQL = @IndexSQL + ' AND si.Name = ''' + @IndexName + '''' | |
| END | |
| SET @IndexSQL = @IndexSQL + ' ORDER BY ObjectName, IndexName, sic.Key_Ordinal' | |
| --PRINT @IndexSQL | |
| -------------INSERT THE INDEX DATA INTO A VARIABLE---------------------- | |
| BEGIN TRY | |
| INSERT INTO @WholeIndexData | |
| EXEC sp_executesql @IndexSQL | |
| END TRY | |
| BEGIN CATCH | |
| SELECT @ErrorMessage = ERROR_MESSAGE() | |
| GOTO ABEND | |
| END CATCH | |
| --Check if any indexes are there on the object. Otherwise exit | |
| IF (SELECT COUNT(*) FROM @WholeIndexData) = 0 | |
| BEGIN | |
| SELECT 'Object does not have any nonclustered indexes to move' | |
| GOTO FINAL | |
| END | |
| -------------Get the distinct index rows in to a variable---------------------- | |
| INSERT INTO @DistinctIndexData | |
| SELECT DISTINCT | |
| ObjectName | |
| ,IndexName | |
| ,Is_Unique | |
| ,Type_Desc | |
| ,Is_Padded | |
| ,[Ignore_Dup_Key] | |
| ,[Allow_Row_Locks] | |
| ,[Allow_Page_Locks] | |
| ,Fill_Factor | |
| ,FileGroupName | |
| ,Has_Filter | |
| ,Filter_Definition | |
| FROM @WholeIndexData | |
| WHERE ObjectName = @ObjectName; | |
| SELECT @CurLoopCount = Min(Id), @MaxLoopCount = Max(Id) FROM @DistinctIndexData WHERE ObjectName = @ObjectName | |
| --SELECT @CurLoopCount, @MaxLoopCount | |
| -------------Loop till all the indexes are processed---------------------- | |
| WHILE(@CurLoopCount <= @MaxLoopCount) | |
| BEGIN | |
| SET @IndexKeySQL = '' | |
| SET @IncludeColSQL = '' | |
| -------------Get the current index row to be processed---------------------- | |
| SELECT | |
| @IndName = IndexName | |
| ,@Type = Type_Desc | |
| ,@ExistingFGName = FileGroupName | |
| ,@IsUnique = CASE WHEN Is_Unique = 1 THEN 'UNIQUE ' ELSE '' END | |
| ,@IsPadded = CASE WHEN Is_Padded = 0 THEN 'OFF,' ELSE 'ON,' END | |
| ,@IgnoreDupKey = CASE WHEN Ignore_Dup_Key = 0 THEN 'OFF,' ELSE 'ON,' END | |
| ,@AllowRowLocks = CASE WHEN Allow_Row_Locks = 0 THEN 'OFF,' ELSE 'ON,' END | |
| ,@AllowPageLocks = CASE WHEN Allow_Page_Locks = 0 THEN 'OFF,' ELSE 'ON,' END | |
| ,@FillFactor = CASE WHEN Fill_Factor = 0 THEN 100 ELSE Fill_Factor END | |
| ,@FilterDef = CASE WHEN Has_Filter = 1 THEN (' WHERE ' + Filter_Definition) ELSE '' END | |
| FROM @DistinctIndexData | |
| WHERE Id = @CurLoopCount | |
| -------------Check if the index is already not part of that FileGroup---------------------- | |
| IF(@ExistingFGName = @FileGroupName) | |
| BEGIN | |
| PRINT 'Index ' + @IndName + ' is NOT moved as it is already part of the FileGroup ' + @FileGroupName + '.' | |
| SET @CurLoopCount = @CurLoopCount + 1 | |
| CONTINUE | |
| END | |
| ------- Construct the Index key string along with the direction-------------------- | |
| SELECT @IndexKeySQL = CASE | |
| WHEN @IndexKeySQL = '' | |
| THEN ( | |
| @IndexKeySQL + QUOTENAME(ColumnName) + CASE | |
| WHEN Is_Descending_Key = 0 | |
| THEN ' ASC' | |
| ELSE ' DESC' | |
| END | |
| ) | |
| ELSE ( | |
| @IndexKeySQL + ',' + QUOTENAME(ColumnName) + CASE | |
| WHEN Is_Descending_Key = 0 | |
| THEN ' ASC' | |
| ELSE ' DESC' | |
| END | |
| ) | |
| END | |
| FROM @WholeIndexData | |
| WHERE ObjectName = @ObjectName | |
| AND IndexName = @IndName | |
| AND Is_Included_Column = 0 | |
| ORDER BY key_ordinal ASC | |
| --PRINT @IndexKeySQL | |
| ------ Construct the Included Column string -------------------------------------- | |
| SELECT | |
| @IncludeColSQL = | |
| CASE | |
| WHEN @IncludeColSQL = '' THEN (@IncludeColSQL + QUOTENAME(ColumnName)) | |
| ELSE (@IncludeColSQL + ',' + QUOTENAME(ColumnName)) | |
| END | |
| FROM @WholeIndexData | |
| WHERE ObjectName = @ObjectName | |
| AND IndexName = @IndName | |
| AND Is_Included_Column = 1 | |
| ORDER BY key_ordinal ASC | |
| --PRINT @IncludeColSQL | |
| -------------Construct the final Create Index statement---------------------- | |
| SELECT | |
| @FinalSQL = 'CREATE ' + @IsUnique + @Type + ' INDEX ' + QUOTENAME(@IndName) | |
| + ' ON ' + QUOTENAME(@DBName) + '.' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@ObjectName) | |
| + '(' + @IndexKeySQL + ') ' | |
| + CASE WHEN LEN(@IncludeColSQL) <> 0 THEN 'INCLUDE(' + @IncludeColSQL + ') ' ELSE '' END | |
| + @FilterDef | |
| + ' WITH (' | |
| + 'PAD_INDEX = ' + @IsPadded | |
| + 'IGNORE_DUP_KEY = ' + @IgnoreDupKey | |
| + 'ALLOW_ROW_LOCKS = ' + @AllowRowLocks | |
| + 'ALLOW_PAGE_LOCKS = ' + @AllowPageLocks | |
| + 'SORT_IN_TEMPDB = OFF,' | |
| + 'DROP_EXISTING = ON,' | |
| + 'ONLINE = OFF,' | |
| + 'FILLFACTOR = ' + CAST(@FillFactor AS Varchar(3)) | |
| + ') ON ' + QUOTENAME(@FileGroupName) | |
| --PRINT @FinalSQL | |
| -------------Execute the Create Index statement to move to the specified filegroup---------------------- | |
| BEGIN TRY | |
| EXEC sp_executesql @FinalSQL | |
| END TRY | |
| BEGIN CATCH | |
| SELECT @ErrorMessage = ERROR_MESSAGE() | |
| GOTO ABEND | |
| END CATCH | |
| PRINT 'Index ' + @IndName + ' on Object ' + @ObjectName + ' is moved successfully.' | |
| SET @CurLoopCount = @CurLoopCount + 1 | |
| END | |
| SET @StartPos = @StartPos + 1 | |
| END | |
| SELECT 'The procedure completed successfully.' | |
| RETURN | |
| ABEND: | |
| RAISERROR (@ErrorMessage, 16, 1); | |
| FINAL: | |
| RETURN | |
| END | |
| GO | |
| */ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment