Script to Move Indexes off of the Primary Filegrou

create procedure moveindexes   
 @CPATH varchar(100) = 'D:\SQLCINDEX\',   
 @NCPATH varchar(100) = 'D:\SQLINDEX\',   
 @numcfiles int = 1,  
 @numncfiles int = 1,  
 @startsize int = 1000,  
 @maxsize int = 10000,  
 @growthsize int = 1000  
as  
  
 declare @objid int,   -- the object id of the table    
   @indid smallint, -- the index id of an index    
   @groupid smallint,  -- the filegroup id of an index    
   @indname sysname,    
   @groupname sysname,    
   @status int,    
   @keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)    
   @dbname sysname  ,  
   @objname nvarchar(776),  
   @FILLFACT TINYINT,  
   @ISQL VARCHAR(5000),  
   @kill int,  
   @counter int  
     
set @counter = 1  
set @dbname = (select name from master..sysdatabases d, master..sysprocesses p where spid = @@spid and d.dbid = p.dbid )  
  
declare kill_cursor cursor for select spid from master..sysprocesses  where spid > 49 and spid <>  @@spid and dbid = (select dbid from master..sysdatabases where name = @dbname)  
open kill_cursor  
fetch next from kill_cursor into @kill  
while @@fetch_status = 0  
begin  
    set @isql = 'kill '+convert(varchar(5),@kill)  
    exec (@isql)  
    fetch next from kill_cursor into @kill  
end  
close kill_cursor  
deallocate kill_cursor  
exec master..sp_dboption @dbname, 'Single User', 'TRUE'  
print 'Database '+@dbname+' Has been set to Single User Only'  
select rtrim(name)as name into ##dbfiles from sysfiles where substring(fn_replinttobitstring(status),12,1) = 1 and fileid <> (select min(fileid) from sysfiles where groupid = 1)  
  
set @isql = 'ALTER DATABASE '+@dbname+ ' ADD FILEGROUP CINDEX'  
exec (@isql)  
print 'File Group CINDEX Has been Created'  
exec (@isql)  
set @isql = 'ALTER DATABASE '+@dbname+ ' ADD FILEGROUP NCINDEX'  
exec (@isql)  
print 'File Group NCINDEX Has been Created'  
exec (@isql)  
  
  
while @counter <= @numcfiles  
begin  
   set @isql = 'ALTER DATABASE '+@dbname+ ' ADD File (Name = CINDEXData'+convert(varchar(2), @counter)+',   FILENAME = ' + '"' + @CPATH +'CINDEXData'+convert(varchar(2), @counter)+'.ndf'+'"'+',  SIZE = '+convert(varchar(5),@startsize) + 'MB, MAXSIZE = '+convert(varchar(5),@maxsize)+'MB, FILEGROWTH = '+convert(varchar(5),@growthsize)+'MB) TO FILEGROUP CINDEX'  
   exec (@isql)  
   print 'File CINDEXDATA'+convert(varchar(2), @counter)+' has been created'  
   set @counter = @counter + 1  
end  
set @counter = 1  
while @counter <= @numncfiles  
begin  
   set @isql = 'ALTER DATABASE '+@dbname+ ' ADD File (Name = NCINDEXData'+convert(varchar(2), @counter)+',   FILENAME = ' + '"' + @NCPATH +'NCINDEXData'+convert(varchar(2), @counter)+'.ndf'+'"'+',  SIZE = '+convert(varchar(5),@startsize) + 'MB, MAXSIZE = '+convert(varchar(5),@maxsize)+'MB, FILEGROWTH = '+convert(varchar(5),@growthsize)+'MB) TO FILEGROUP NCINDEX'  
   exec (@isql)  
   print 'File NCINDEXDATA'+convert(varchar(2), @counter)+' has been created'  
   set @counter = @counter + 1  
end  
  
  
exec master..sp_dboption @dbname, 'trunc. log on chkpt.', 'TRUE'  
print ' Database ' +@dbname + ' Has been set to simple Recovery Mode'  
DECLARE TCURSOR CURSOR FOR SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U' ORDER BY NAME ASC  
OPEN TCURSOR  
FETCH NEXT FROM TCURSOR INTO @OBJNAME  
WHILE @@FETCH_STATUS = 0  
BEGIN  
  
   
   select @objid = object_id(@objname)    
   create table #spindtab    
     (    
     index_name   sysname collate database_default NOT NULL,    
     stats    int,    
     groupname   sysname collate database_default NOT NULL,    
     index_keys   nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr    
     FILLFACT TINYINT  
     )    
  
  declare ms_crs_ind cursor local static for    
  select indid, groupid, name, status, ORIGFILLFACTOR from sysindexes    
     where id = @objid and indid > 0 and indid < 255 and (status & 64)=0 order by indid    
 open ms_crs_ind    
 fetch ms_crs_ind into @indid, @groupid, @indname, @status, @FILLFACT  
    
 while @@fetch_status >= 0    
 begin    
  declare @i int, @thiskey nvarchar(131) -- 128+3    
    
  select @keys = index_col(@objname, @indid, 1), @i = 2    
  if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)    
   select @keys = @keys  + '(-)'    
    
  select @thiskey = index_col(@objname, @indid, @i)    
  if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))    
   select @thiskey = @thiskey + '(-)'    
    
  while (@thiskey is not null )    
  begin    
   select @keys = @keys + ', ' + @thiskey, @i = @i + 1    
   select @thiskey = index_col(@objname, @indid, @i)    
   if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))    
    select @thiskey = @thiskey + '(-)'    
  end    
    
  select @groupname = groupname from sysfilegroups where groupid = @groupid    
    
  insert into #spindtab values (@indname, @status, @groupname, @keys, @FILLFACT)    
    
  -- Next index    
  fetch ms_crs_ind into @indid, @groupid, @indname, @status, @FILLFACT    
 end    
 deallocate ms_crs_ind    
    
 -- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY    
 declare @empty varchar(1) select @empty = ''    
 declare @des1   varchar(35), -- 35 matches spt_values    
   @des2   varchar(35),    
   @des4   varchar(35),    
   @des32   varchar(35),    
   @des64   varchar(35),    
   @des2048  varchar(35),    
   @des4096  varchar(35),    
   @des8388608  varchar(35),    
   @des16777216 varchar(35)    
 select @des1 = name from master.dbo.spt_values where type = 'I' and number = 1    
 select @des2 = name from master.dbo.spt_values where type = 'I' and number = 2    
 select @des4 = name from master.dbo.spt_values where type = 'I' and number = 4    
 select @des32 = name from master.dbo.spt_values where type = 'I' and number = 32    
 select @des64 = name from master.dbo.spt_values where type = 'I' and number = 64    
 select @des2048 = name from master.dbo.spt_values where type = 'I' and number = 2048    
 select @des4096 = name from master.dbo.spt_values where type = 'I' and number = 4096    
 select @des8388608 = name from master.dbo.spt_values where type = 'I' and number = 8388608    
 select @des16777216 = name from master.dbo.spt_values where type = 'I' and number = 16777216    
    
 -- DISPLAY THE RESULTS    
declare @tcount int  
set @tcount = (select count(*) from sysobjects where type = 'U' and name > @objname)  
print 'Recreating Indexes for Table ' + @objname + '.  ' + convert(varchar(5),@tcount) + ' Tables Remaining!'  
DECLARE @INAME VARCHAR(100)  
DECLARE ICURSOR CURSOR FOR SELECT INDEX_NAME FROM #SPINDTAB ORDER BY INDEX_NAME   
OPEN ICURSOR  
FETCH NEXT FROM ICURSOR INTO @INAME  
WHILE @@FETCH_STATUS = 0   
BEGIN  
 SET @ISQL = ( select  'Create  '  + case when (stats & 2)<>0 then @des2 + '  ' else @empty end +   
 case when (stats & 16)<>0 then 'CLUSTERED  INDEX ' else ' INDEX ' end +  
 index_name + ' ON ' + @objname + ' ('+index_keys +') WITH '+  
   CASE WHEN FILLFACT = 0 THEN ' DROP_EXISTING' ELSE ' FILLFACTOR = '+CONVERT(CHAR(2),FILLFACT)+', DROP_EXISTING' END +  
 ' ON ' + case when (stats & 16)<>0 then ' [CINDEX]' else ' [NCINDEX]' end  
 from #spindtab  WHERE INDEX_NAME = @INAME)  
  
exec (@ISQL)  
FETCH NEXT FROM ICURSOR INTO @INAME  
END  
CLOSE ICURSOR  
DEALLOCATE ICURSOR  
  
drop table #spindtab  
FETCH NEXT FROM TCURSOR INTO @OBJNAME  
END  
CLOSE TCURSOR  
DEALLOCATE TCURSOR  
  
  
exec master..sp_dboption @dbname, 'trunc. log on chkpt.', 'FALSE'    
print 'Database ' +@dbname + ' Has been set to full Recovery Mode'  
  
  
exec master..sp_dboption @dbname, 'Single User', 'False'  
print 'Database '+@dbname+' Has been set back to Multi User'  
  
select getdate()  
  
  
  
declare @fname varchar(50)  
declare @sql varchar(1000)  
-- declare @dbname varchar(50)  
declare file_cursor cursor for select name from ##dbfiles  
set @dbname = (select name from master..sysdatabases d, master..sysprocesses p where spid = @@spid and d.dbid = p.dbid )  
open file_cursor  
fetch next from file_cursor into @fname  
while @@fetch_status = 0  
begin  
set @sql = 'dbcc shrinkfile ('+@fname+', emptyfile)'  
exec (@sql)  
print @fname + ' Has been emptied'  
set @sql = 'ALTER DATABASE '+@dbname +' REMOVE FILE '+@fname  
exec(@sql)  
print @fname + ' Has been removed'  
fetch next from file_cursor into @fname  
end  
close file_cursor  
deallocate file_cursor