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 
