quinta-feira, 11 de agosto de 2011

Reconstruir Indices no SQL Server 2008

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[function_clean_rebuild_indexes]
AS
BEGIN
-- FILLFACTOR 90
declare @TableName varchar(255)
declare @sql nvarchar(500)
declare @fillfactor int, @tot float, @now float
SET @fillfactor = 90

set @tot =
(select count(OBJECT_SCHEMA_NAME([object_id])+'.'+name) AS TableName
from sys.tables)
declare TableCursor cursor for
select OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
from sys.tables
order by 1

open TableCursor
fetch next from TableCursor into @TableName
print 'Iniciando ALTER INDEX ALL (FILL FACTOR '+cast(@fillfactor as varchar(10))+')...'

set @now = 0
while @@FETCH_STATUS = 0
begin
set @now = @now + 1
print 'Reindexando tabela ' + upper(@TableName) + ' ( '+cast(@now as varchar(10))+'/'+cast(@tot as varchar(10))+' - '+cast(round(@now * 100 / @tot, 2) as varchar(20))+'% ) ...'
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
exec(@sql)
fetch next from TableCursor into @TableName
end
close TableCursor
deallocate TableCursor
print 'Reconstrução dos Índices Terminada.'
END

Nenhum comentário: