Technical Article

DBCC SHOWCONTIG in all Tables

,

Use this script to see showcontig for all tables in the context of the database in which its called. Very handy to schedule during off peak hours prior to any re-indexing to see how bad things are and re-build certain indexes sooner than you thought for peak performance.

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

sql-scripting 08/20/2001 

email: sqlscripters@sql-scripting.com  

Visit www.sql-scripting.com 

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

USE MASTER

GO

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON 

GO 

CREATE PROCEDURE sp_DBA_DBCCShowFragAll  

AS 

BEGIN 

DECLARE UserTables INSENSITIVE CURSOR 

     FOR 

  Select name FROM sysobjects --select table names

  WHERE type = 'U' 

  ORDER BY name 

FOR READ ONLY 

OPEN UserTables 

DECLARE @TableName varchar(50), 

                  @MSG varchar(255), 

                  @id int 

FETCH NEXT FROM UserTables INTO @TableName --pass tbl names

   WHILE (@@FETCH_STATUS = 0)--loop through tablenames 

      BEGIN 

SELECT @MSG = 'DBCC SHOWCONTIG For table: ' + @TableName 

 PRINT @MSG --print some info

SET @id = object_id(@tablename)--set variable to pass 

DBCC SHOWCONTIG (@id) --execute

 PRINT '' 

FETCH NEXT FROM UserTables INTO @TableName 

END 

CLOSE UserTables 

DEALLOCATE UserTables 

END 

GO 

SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON 

GO

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating