Last active
August 16, 2023 15:08
-
-
Save dalenewman/6377911 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 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
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
I would love to see this also move the Unique Keys and Primary Keys, but I'm unsure how to check things in.
Here is the code I wrote to add Unique Keys and Primary Keys:
/* FIX all Unique constraints and Primary Key constraints */
DECLARE @Sql NVARCHAR(max)='', @Compression INT = 0, @Filegroup NVARCHAR(max) = '[PRIMARY]'
SELECT @Compression = SUBSTRING(@@Version, 22, 4) + CASE WHEN @@Version LIKE '%Enterprise Edition%' THEN 1000 ELSE 0 END
SET @Filegroup = '[Standard]'
--SELECT @Compression , @@Version
--how to enforce compression: SELECT @Compression=2016
SELECT @Sql += 'ALTER TABLE ' + a.SchemaName + '.' + a.TableName + ' DROP CONSTRAINT ' + QUOTENAME(a.KeyName) + '
ALTER TABLE ' + a.SchemaName + '.' + a.TableName + ' ADD CONSTRAINT ' + QUOTENAME(a.KeyName )
WHEN type = 'PK' THEN ' PRIMARY KEY ' + a.IsClustered + '(' + A.columns + ') '
WHEN type = 'UQ' THEN ' UNIQUE ' + a.IsClustered + ' (' + A.columns + ') '
ELSE ' unknown type '
END
'
FROM (
SELECT kc.type, kc.name AS KeyName, s.name AS SchemaName, t.name AS TableName
, (STUFF((SELECT ', ' + c.name
FROM sys.index_columns ic
INNER JOIN sys.columns c ON ic.object_id=c.object_id AND ic.column_id=c.column_id
WHERE kc.parent_object_id=ic.object_id AND kc.unique_index_id=ic.index_id
ORDER BY ic.key_ordinal FOR XML PATH('')),1,2, '')
) AS columns
, i.type_desc AS IsClustered
FROM sys.key_constraints kc
INNER JOIN sys.tables t ON t.object_id = kc.parent_object_id
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN sys.indexes i ON i.object_id = kc.parent_object_id AND i.Index_id = kc.unique_index_id
) AS a
SELECT @Sql