dropping and rebuilding indexes using MSForEachDB

[BikeBoy20032004]

Hi all,

I have this proc for rebuilding indexes on a different filegroup. It works in a specific database, but I want it to iterate through all databases on the server. Here I can't use dbcc reindex, have to write my own.

I am trying to use sp_MSForEachDB, with no luck. I think it's quotation error somewhere. Here's the code, Can yuo please help?

set nocount on


declare @TableName sysname, @IndexName sysname, @ColumnString varchar(1024), @SQL varchar(1024),
@SQL3 varchar(1024), @CMD varchar(1024)


set @CMD = 'Use ? select distinct so.[name] TableName, si.[name] IndexName, sfg.groupname [FileGroup], so.[id] TableID--, sik.colid
into #Indexes
from sysindexes si
join sysindexkeys sik on si.[id] = sik.[id] and si.indid = sik.indid
join sysobjects so on si.[id] = so.[id]
join sysfilegroups sfg on si.groupid = sfg.groupid
where si.indid between 2 and 254
and so.type = ''U''
and si.[name] not like ''_WA_%''
and so.[name] <> ''dtproperties''
and sfg.groupname = ''primary''
and si.[name] not like ''PK%''

order by so.[name], si.[name]' + char (10) +





'declare IndexLoop cursor for
select TableName, IndexName
from #Indexes ' + char (10) +

'open IndexLoop
fetch next from IndexLoop into @TableName, @IndexName
while @@fetch_status = 0' + char (10) +
'begin

set @ColumnString = ''
select @ColumnString = @ColumnString + sc.[name] + '',''
from sysindexes si
join sysindexkeys sik on si.[id] = sik.[id] and si.indid = sik.indid
join sysobjects so on si.[id] = so.[id]
join syscolumns sc on so.[id] = sc.[id] and sik.colid = sc.colid
join sysfilegroups sfg on si.groupid = sfg.groupid
where si.indid between 2 and 254
and si.[name] = @IndexName
and so.[name] = @TableName
order by sc.colid

set @ColumnString = left(@ColumnString, len(@ColumnString)-1)' + char (10) +



'set @SQL =
''if exists (select * from dbo.sysindexes where name = N'''' + @IndexName +'''' + char(10) +
'' and id = object_id(N'''' + ''[dbo].['' + @TableName + '']'''' + ''))'' + char(10) ' + char (10) +

'set @sql3 = ''create index '' + ''['' + @IndexName + '']'' + '' on '' + ''['' + @TableName + '']'' + ''('' + @ColumnString + '')
WITH DROP_EXISTING on [INDEX]'' + char(10)
set @sql = @sql + @SQL3


print @sql ' + char (10) +
--execute (@SQL)




'fetch next from IndexLoop into @TableName, @IndexName
end' + char(10) +
'close IndexLoop' + char(10) +
'deallocate IndexLoop' + char(10) +


'drop table #Indexes ' + char(10) +

exec sp_MSForEachDB @CMD

set nocount off
GO

 

[JayKusch]

This may get you a bit farther. It Drops and ReCreates the indexes on all the tables w/in a database. Once created, you could then implement it using the sp_MSForEachDB or create a job w/ a step per DB and call this proc.

CREATE PROC usp_index_rebuild
AS
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
  FROM information_schema.tables
  WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT 'Reindexing ' + @TableName + ' table'
  DBCC DBREINDEX (@TableName, ' ', 80)
  FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO

Thanks

J. Kusch

 

[BikeBoy20032004]

Thanks for the responce, JayKusch

I could create a job w/ a step per DB. But my problem is precisely how to implement my code usong sp_MSForEachDB, to iterate through each user db.

As you can see, the code only rebuilds non-clustered indexes that are not PKs.