gistfile1.sql

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

Thanx a lot you save me a day