Skip to content

Instantly share code, notes, and snippets.

@psteffek
Forked from dalenewman/gist:6377911
Last active August 16, 2023 15:07

Revisions

  1. psteffek revised this gist Oct 7, 2013. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion gistfile1.sql
    Original file line number Diff line number Diff line change
    @@ -378,7 +378,10 @@ END
    RETURN

    ABEND:
    RAISERROR 500001 @ErrorMessage
    RAISERROR (@ErrorMessage, -- Message text.
    16, -- Severity.
    1 -- State.
    );
    FINAL:
    RETURN
    END
  2. @dalenewman dalenewman created this gist Aug 29, 2013.
    386 changes: 386 additions & 0 deletions gistfile1.sql
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,386 @@
    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 500001 @ErrorMessage
    FINAL:
    RETURN
    END

    GO