quinta-feira, 11 de agosto de 2011

Reconstruir Indices no SQL Server 2000/2005

CREATE PROCEDURE [dbo].[function_clean_rebuild_indexes]
AS
BEGIN
SET ARITHABORT ON
SET QUOTED_IDENTIFIER ON

declare @tabname sysname
declare @dbstring varchar(300)
declare @exec_string varchar(300)

declare tabDBCC cursor for select table_name from information_schema.tables where table_type = 'base table'

open tabDBCC
fetch next from tabDBCC into @tabname

select @dbstring = DB_NAME()
print 'Iniciando DBCC DBREINDEX para a base ' + upper(@dbstring) + '...'

while (@@fetch_status = 0)
begin
print 'Reindexando tabela ' + upper(@tabname)
select @exec_string = 'dbcc dbreindex ([' + @tabname + '])'
exec(@exec_string)
fetch next from tabDBCC into @tabname
end

close tabDBCC
deallocate tabDBCC
print 'Reconstrução dos Índices Terminada.'
END
GO

Nenhum comentário: