<div>
The script below creates a stored procedure which checks the fragmentation of all indexes on the database it's run on. You can set the amount of fragmentation that is considered in need of correction and the script can automatically take corrective action by defragmenting, or rebuilding indexes. 
 
I've set this up procedure up on all my databases that I want to be monitored, and they're run as part of my weekly scheduled maintenance. 

<pre><code class="code-100 nolinks" id="code-124988">SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 
/*
toolDefragmentDatabase
 
Author
DJH	10-Dec-2007
 
Purpose
When run this procedure examines all the indexes in the current database, looking for those 
which are fragmented and in need of maintenance. It can then perform corrective action if 
desired.
 
Method
The procedure builds a table of fragmentation indicators by running the DBCC SHOWCONTIG command 
on each candidate index. This table is then used to dynamically script appropriate defragmentation
actions, based on the parameter values supplied.
 
Parameters
Name			Type	 	Default		Description	
@UB_LFrag_NoAction	Percentage	10		This parameter indicates the minimum logical 
							fragmentation % neccessary to trigger defragmentation. 
@UB_LFrag_Defrag	Percentage	30		This parameter indicates the mimumum logical
							fragmentation % neccessary to trigger a full rebuild, 
							rather than a defragmentation.
@MinPages		Integer		50		Indexes with fewer than this number of data pages
							will not be considered for reindexing.
@AnalyseResults		Boolean		1		If true the procedure generates a report 
							indicating what indexes have been defragged, 
							and how effective the defragmentation was.
@PredictOnly		Boolean		0		If set to true, no defragmentation is performed, but a 
							prediction of what would be defragmented and how is returned.
*/
 
CREATE PROCEDURE toolDefragmentDatabase
 	(
	@UB_LFrag_NoAction float = NULL,
	@UB_LFrag_Defrag float = NULL,
	@MinPages int = NULL,
	@AnalyseResults bit = NULL,
	@PredictOnly bit = NULL
	)
 
AS
 
SELECT
	@UB_LFrag_NoAction = ISNULL(@UB_LFrag_NoAction, 10),
	@UB_LFrag_Defrag = ISNULL(@UB_LFrag_Defrag, 30),
	@MinPages = ISNULL(@MinPages, 50),
	@AnalyseResults = ISNULL(@AnalyseResults, 1),
	@PredictOnly = ISNULL(@PredictOnly, 0)
 
IF @UB_LFrag_NoAction NOT BETWEEN 0 AND 100
BEGIN
	RAISERROR('@UB_LFrag_NoAction must be between 0 and 100', 14, 1)
	RETURN -1
END
 
IF @UB_LFrag_Defrag NOT BETWEEN @UB_LFrag_NoAction AND 100
BEGIN
	RAISERROR ('@UB_LFrag_Defrag should be between @UB_LFrag_NoAction and 100.', 14, 1)
	RETURN -1
END
 
IF @MinPages &lt; 0
BEGIN
	RAISERROR('The mininum number of pages to consider should be greater than or equal to 0', 14, 1)
	RETURN -1
END
 
SET NOCOUNT ON
 
CREATE TABLE #PreOptimFrag
(
ObjectName sysname,
ObjectId int NOt NULL,
IndexName sysname,
IndexId int NOT NULL,
Level int,
Pages int,
Rows int,
MinimumRecordSize float,
MaximumRecordSize float,
AverageRecordSize float,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
AverageFreeBytes float,
AveragePageDensity float,
ScanDensity float,
BestCount int,
ActualCount int,
LogicalFragmentation float,
ExtentFragmentation float
)
 
CREATE TABLE #PostOptimFrag
(
ObjectName sysname,
ObjectId int NOt NULL,
IndexName sysname,
IndexId int NOT NULL,
Level int,
Pages int,
Rows int,
MinimumRecordSize float,
MaximumRecordSize float,
AverageRecordSize float,
ForwardedRecords int,
Extents int,
ExtentSwitches int,
AverageFreeBytes float,
AveragePageDensity float,
ScanDensity float,
BestCount int,
ActualCount int,
LogicalFragmentation float,
ExtentFragmentation float
)
 
DECLARE @ObjectName sysname,
	@IndexName sysname
 
DECLARE c_GetFragmentation CURSOR
LOCAL FAST_FORWARD
FOR 
SELECT
	OBJECT_NAME(si.id),
	si.name 
FROM 
	dbo.sysindexes si 
WHERE 
	INDEXPROPERTY(si.id, si.name, 'IsStatistics') = 0
AND	OBJECTPROPERTY(si.id, 'IsMSShipped') = 0
AND	si.indid BETWEEN 1 AND 244
AND	si.rows &gt; 0
ORDER BY	
	OBJECT_NAME(si.id),
	si.name 
 
OPEN c_GetFragmentation
 
FETCH NEXT FROM c_GetFragmentation INTO @ObjectName, @IndexName
WHILE (@@fetch_status &lt;&gt; -1)
BEGIN
	IF (@@fetch_status &lt;&gt; -2)
	BEGIN
		INSERT #PreOptimFrag
		EXEC('DBCC SHOWCONTIG (''' + @ObjectName + ''', ''' + @IndexName + ''') WITH TABLERESULTS, FAST')
	END
	FETCH NEXT FROM c_GetFragmentation INTO @ObjectName, @IndexName
END
 
CLOSE c_GetFragmentation
DEALLOCATE c_GetFragmentation
 
IF @PredictOnly = 1
BEGIN
	SELECT
		p.ObjectName as 'Object Name',
		p.IndexName as 'Index Name',
		p.Pages as 'Pages', 
		p.LogicalFragmentation as 'Logical Fragmentation (%)',
		CASE 
			WHEN p.LogicalFragmentation &lt; @UB_LFrag_Defrag THEN 'Defrag'
			ELSE 'Re-Index'
		END as 'Action'
	FROM
		#PreOptimFrag p
	WHERE
		p.LogicalFragmentation &gt;= @UB_LFrag_NoAction
	AND	p.Pages &gt;= @MinPages
 
	GOTO EndScript
END
 
DECLARE c_DEFRAG CURSOR
LOCAL FAST_FORWARD
FOR 
SELECT
	p.ObjectName,
	p.IndexName
FROM
	#PreOptimFrag p
WHERE
	p.LogicalFragmentation &gt;= @UB_LFrag_NoAction
AND	p.LogicalFragmentation &lt; @UB_LFrag_Defrag
AND	p.Pages &gt;= @MinPages
 
OPEN c_DEFRAG
 
FETCH NEXT FROM c_DEFRAG INTO @ObjectName, @IndexName
WHILE (@@fetch_status &lt;&gt; -1)
BEGIN
	IF (@@fetch_status &lt;&gt; -2)
	BEGIN
		DBCC INDEXDEFRAG(0, @ObjectName, @IndexName) WITH NO_INFOMSGS
		IF @AnalyseResults = 1
		BEGIN
			INSERT #PostOptimFrag
			EXEC ('DBCC SHOWCONTIG (''' + @ObjectName + ''', ''' + @IndexName + ''') WITH TABLERESULTS, FAST')
		END
	END
	FETCH NEXT FROM c_DEFRAG INTO @ObjectName, @IndexName
END
 
CLOSE c_DEFRAG
DEALLOCATE c_DEFRAG
 
 
DECLARE c_REINDEX CURSOR
LOCAL FAST_FORWARD
FOR 
SELECT
	p.ObjectName,
	p.IndexName
FROM
	#PreOptimFrag p
WHERE
	p.LogicalFragmentation &gt;= @UB_LFrag_Defrag
AND	p.Pages &gt;= @MinPages
 
OPEN c_REINDEX
 
FETCH NEXT FROM c_REINDEX INTO @ObjectName, @IndexName
WHILE (@@fetch_status &lt;&gt; -1)
BEGIN
	IF (@@fetch_status &lt;&gt; -2)
	BEGIN
		DBCC DBREINDEX(@ObjectName, @IndexName) WITH NO_INFOMSGS
		IF @AnalyseResults = 1
		BEGIN
			INSERT #PostOptimFrag
			EXEC ('DBCC SHOWCONTIG (''' + @ObjectName + ''', ''' + @IndexName + ''') WITH TABLERESULTS, FAST')
		END
	END
	FETCH NEXT FROM c_REINDEX INTO @ObjectName, @IndexName
END
 
CLOSE c_REINDEX
DEALLOCATE c_REINDEX
 
 
IF @AnalyseResults = 1
BEGIN
	SELECT
		pre.ObjectName			as 'Object Name',
		pre.IndexName			as 'Index Name',		
		CASE 
			WHEN pre.LogicalFragmentation &lt; @UB_LFrag_Defrag THEN 'Defrag'
			ELSE 'Re-Index'
		END 				as 'Action',
		pre.Pages 			as 'Pages Pre Script',
		post.Pages 			as 'Pages Post Script',
		pre.LogicalFragmentation	as 'Fragmentation Pre Script',
		post.LogicalFragmentation	as 'Fragmentation Post Script'
	FROM
		#PreOptimFrag pre
		INNER JOIN #PostOptimFrag post ON post.ObjectId = pre.ObjectId
			AND post.IndexId = pre.IndexId
END
 
 
EndScript:
DROP TABLE #PreOptimFrag
DROP TABLE #PostOptimFrag
SET NOCOUNT OFF
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON 
GO
</code></pre>
</div>

<div>
<div class="content wysiwyg-content">
I'm currently in the process of creating a custom script for System Center Operations Manager that will monitor the index fragmentation for our SQL servers. 
 
I've gotten the monitor up and running for SQL Server 2005 but I am having trouble with one for 2000. 
 
Is there any way to check the index fragmentation in all DBs on a server in 2000? If not, is there a way using vbs/opsmgr to cycle through each 2000 database? 
 
Thanks in advance
</div>
</div>

I'm currently in the process of creating a custom script for System Center Operations Manager that will monitor the index fragmentation for our SQL servers.

I've gotten the monitor up and running for SQL Server 2005 but I am having trouble with one for 2000. 

Is there any way to check the index fragmentation in all DBs on a server in 2000? If not, is there a way using vbs/opsmgr to cycle through each 2000 database?

Thanks in advance

How to check index fragmentation on server from master database

The Microsoft Server topic includes all of the legacy versions of the operating system, including the Windows NT 3.1, NT 3.5, NT 4.0 and Windows 2000 and Windows Home Server versions.