EXEC MoveIndexToFileGroup @DBName = ”,
@SchemaName = ”,
@ObjectNameList = ”,
@IndexName = ”,
@FileGroupName = ”;
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MoveIndexToFileGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MoveIndexToFileGroup]
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)
)
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,0 as Has_Filter,N”” as Filter_Definition 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 in to 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
–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
–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, — Message text.
16, — Severity.
1 — State.
);
FINAL:
RETURN
END
GO