ShowContig.sql

,

This script can be used on SQL Server 2000 databases.  Some minor modifications are needed for SQL Server 7.0.  This will report the DBCC SHOWCONTIG values for all the user tables, in all the databases on the server. 

/*******************************************************************************
*  File:   ShowContig.sql
*  Author: Jeff Weisbecker  2002
*          Jeff.Weisbecker@mindspring.com
*  Description: Reports fragmentation on all the user tables on a SQL Server
*               2000 database server.
*
*  Notes:  -May not work on SQL Server 7.0
*          -Information regarding specific indexes can be obtain by adding the
*           option to the DBCC SHOWCONTIG statement.
*          -The amount of information returned can be reduced by tailoring
*           the SELECT * FROM #TempForOutput statement.
*          
*
*******************************************************************************/
SET NOCOUNT ON
--
DECLARE @DBName       NVARCHAR(20)
DECLARE @SQLString    NVARCHAR(2000)
DECLARE @TableName    VARCHAR(40)
DECLARE @DatabaseName VARCHAR(40)
--
CREATE TABLE #TempForShowContig(
                        ObjectName      CHAR (60),
                        ObjectId        INT,
                        IndexName       CHAR (60),
                        IndexId         INT,
                        Lvl             INT,
                        CountPages      INT,
                        CountRows       INT,
                        MinRecSize      INT,
                        MaxRecSize      INT,
                        AvgRecSize      INT,
                        ForRecCount     INT,
                        Extents         INT,
                        ExtentSwitches  INT,
                        AvgFreeBytes    INT,
                        AvgPageDensity  INT,
                        ScanDensity     DECIMAL,
                        BestCount       INT,
                        ActualCount     INT,
                        LogicalFrag     DECIMAL,
                        ExtentFrag      DECIMAL)
--
CREATE TABLE #TempForTableName (TableName   VARCHAR(40))
--
CREATE TABLE #TempForOutput(
                        ServerName      VARCHAR(40),
                        DatabaseName    VARCHAR(20),
                        ObjectName      CHAR (60),
                        Lvl             INT,
                        CountPages      INT,
                        CountRows       INT,
                        MinRecSize      INT,
                        MaxRecSize      INT,
                        AvgRecSize      INT,
                        ForRecCount     INT,
                        Extents         INT,
                        ExtentSwitches  INT,
                        AvgFreeBytes    INT,
                        AvgPageDensity  INT,
                        ScanDensity     DECIMAL,
                        BestCount       INT,
                        ActualCount     INT,
                        LogicalFrag     DECIMAL,
                        ExtentFrag      DECIMAL,
                        DateChecked     DATETIME)
--
DECLARE c_db CURSOR FOR
    SELECT name
    FROM master.dbo.sysdatabases
    WHERE status&512 = 0
      AND name NOT IN ('master', 'msdb', 'model', 'tempdb', 'pubs', 'Northwind','PRD','DEV')
 
DECLARE c_table CURSOR FOR
   SELECT TableName
   FROM #TempForTableName
--
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @SQLString = 'SELECT TABLE_NAME '                                  +
                       'FROM [' + @DBName + '].INFORMATION_SCHEMA.TABLES '     +
                       'WHERE TABLE_TYPE = ''BASE TABLE'''
      INSERT #TempForTableName
      EXECUTE(@SQLString)
      ------------------------------------------------------------------------
      OPEN c_table
      FETCH NEXT FROM c_table INTO @tableName
      WHILE @@FETCH_STATUS = 0
         BEGIN  
             INSERT #TempForShowContig
             EXEC ('USE [' + @DBName + '] DBCC SHOWCONTIG (''' + @tablename + ''') WITH TABLERESULTS, NO_INFOMSGS')
             FETCH NEXT FROM c_table INTO @tableName
         END 
      CLOSE c_table
      --
      INSERT INTO #TempForOutput (ServerName,
                                  DatabaseName,
                                  ObjectName,
                                  Lvl,
                                  CountPages,
                                  CountRows,
                                  MinRecSize,
                                  MaxRecSize,
                                  AvgRecSize,
                                  ForRecCount,
                                  Extents,
                                  ExtentSwitches,
                                  AvgFreeBytes,
                                  AvgPageDensity,
                                  ScanDensity,
                                  BestCount,
                                  ActualCount,
                                  LogicalFrag,
                                  ExtentFrag,
                                  DateChecked)
                         (SELECT @@SERVERNAME,
                                 @DBName,
                                 ObjectName,
                                 Lvl,
                                 CountPages,
                                 CountRows,
                                 MinRecSize,
                                 MaxRecSize,
                                 AvgRecSize,
                                 ForRecCount,
                                 Extents,
                                 ExtentSwitches,
                                 AvgFreeBytes,
                                 AvgPageDensity,
                                 ScanDensity,
                                 BestCount,
                                 ActualCount,
                                 LogicalFrag,
                                 ExtentFrag,
                                 GETDATE()
                          FROM  #TempForShowContig) 
      --
      TRUNCATE TABLE #TempForTableName
      TRUNCATE TABLE #TempForShowContig 
      -------------------------------------------------------------------------
      FETCH NEXT FROM c_db INTO @DBName
   END
DEALLOCATE c_db
DEALLOCATE c_table
SELECT * FROM #TempForOutput
------------
DROP TABLE #TempForShowContig 
DROP TABLE #TempForTableName
DROP TABLE #TempForOutput
--
SET NOCOUNT OFF

Rate

Share

Share

Rate