Show Fragmentation Before and After Reindex

,

This will show the fragmentation and stats before and after a reindex of a specified table. Run in the context for the db that holds the table the proc will get passed and re-index. Complile in master since the master is checked first for any SP that is prefixed with SP_ and you can call it from any db context.

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON

GO 

Create Proc sp_dba_ContigWoutput @t varchar(30)=''

/******************************

sql-scripting 04/04/2001 

email: sqlscripters@sql-scripting.com  

Visit www.sql-scripting.com 

******************************/ 

--With Encryption 

AS

 If @t = '' 

Begin  

      Print'/*********************************************************************'       

      Print CHAR(13)+'Must pass a single table name as input parameter to feed the procedure.' 

      Print Char(13)+'Usage: exec sp_dba_ContigWoutput tablename' 

      Print CHAR(13)+'Edward J Pochinski III 05/07/2001 email:sqlscripters@sql-scripting.com' 

      Print CHAR(13)+'*********************************************************************/'  

RETURN 

End 

DECLARE @id int , @db varchar(30) 

Select @db = db_name() 

      Print 'Viewing DBCC SHOWCONTIG result set for '+@db+'..'+@t+' table prior to re-indexing.' 

      Print '' 

SELECT @id = object_id( @t ) 

DBCC SHOWCONTIG ( @id )   

      Print CHAR(13)+'Below is the statistics for the specified index were last updated prior to the re-index.' 

      Print '' 

SELECT 'Index Name'=i.name,'Statistics Date' = STATS_DATE( i.id, i.indid ) 

FROM sysobjects o JOIN sysindexes i ON o.id = i.id 

      WHERE o.name = @t 

      Print char(13)+'Below is the DBCC SHOWCONTIG result set for the specified index after re-indexing.' 

      Print '' 

DBCC DBREINDEX ( @t )

 -- 

DBCC SHOWCONTIG ( @id ) 

      Print CHAR(13)+'Below is the statistics for the specified index after the re-indexing of '+ db_name() +'..' + @t + '.' 

      Print '' 

SELECT 'Index Name'=i.name, 'Statistics Date'=STATS_DATE( i.id, i.indid ) 

FROM sysobjects o JOIN sysindexes i ON o.id = i.id 

   WHERE o.name = @t 

GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO

Rate

Share

Share

Rate