isitdevops.com/databases

DECLARE @DatabaseName VARCHAR(64)
SELECT @DatabaseName = ‘‘

DECLARE @MaxFragmentationPercent DECIMAL
DECLARE @Mode VARCHAR(10)
DECLARE @FillFactor DECIMAL
DECLARE @Tablename VARCHAR(255)

DECLARE @Command   VARCHAR (255)
DECLARE @ObjectID  INT
DECLARE @IndexName VARCHAR(255)

— Create table to hold list of tables in database
CREATE TABLE #TableList ( TableName VARCHAR(255) )

SELECT @Command = ‘SELECT t1.Name FROM ‘ + @DatabaseName + ‘..sysobjects t1 INNER JOIN ‘ + @DatabaseName + ‘..sysindexes t2 ON t1.id = t2.id WHERE t1.type = ”U” AND t2.indid = 1’

INSERT INTO #TableList
EXEC (@Command)

— Build cursor containing list of tables in database
DECLARE tables CURSOR FOR
SELECT TableName FROM #TableList

— Create table to hold fragmentation statistics
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
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)

OPEN tables

— Loop through all the tables in the tables cursor
FETCH NEXT FROM tables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

— DBCC SHOWCONTIG to get fragmentation stats for indexes on current table
SELECT @Command = ‘USE ‘ + @DatabaseName + ‘; DBCC SHOWCONTIG (”’ + @DatabaseName + ‘..’ + @TableName + ”’, 1 ) WITH FAST,
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS’

INSERT INTO #fraglist
EXEC(@Command)

FETCH NEXT FROM tables INTO @TableName
END

CLOSE tables
DEALLOCATE tables

SELECT CAST(ObjectName as varchar(30)) “ObjectName”, CAST(IndexName as varchar(40)) “IndexName”, CountPages, ScanDensity, BestCount, ActualCount, LogicalFrag
from #fraglist
where CountPages>=128
AND indexid != 0
AND indexid != 255
order by LogicalFrag Desc

DROP TABLE #TableList
DROP TABLE #fraglist