Skip to content

Instantly share code, notes, and snippets.

@psteffek
Forked from dalenewman/gist:6377911
Last active August 16, 2023 15:07
Show Gist options
  • Save psteffek/6877012 to your computer and use it in GitHub Desktop.
Save psteffek/6877012 to your computer and use it in GitHub Desktop.
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
@adopilot
Copy link

Thanx a lot you save me a day

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment