question

Sagar Bhargava avatar image
Sagar Bhargava asked

SQL Server 2000 Indexes

Hi, I am looking to find a list of all indexes in a database on a SQL Server 2000 system and see which all can be Rebuild / Reindexed. Does anyone have a query to do this? Thanks.
indexes
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Hi. I had a similar issue a while back, when looking to build a more sensible index maintenance rebuild strategy on SQL Server 2000, similar to that used by Ola Hallengren's scripts & Michelle Ufford's scripts. I came up with http://thelonedba.wordpress.com/2011/07/29/index-maintenance-for-sql2k/ - nowhere near as comprehensive, but seems to do the job! The guts of it is run from the output of DBCC SHOWCONTIG WITH TABLERESULTS, FAST Hope this helps
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sharma avatar image
Sharma answered

you can use given below script and set logical fragmentation accordingly-

/Perform a 'USE ' to select the database in which to run the script./ -- Declare variables

SET nocount ON;

DECLARE @execstr VARCHAR(400);

DECLARE @objectName VARCHAR(400);

DECLARE @indexName VARCHAR(400);

DECLARE @ID INT

DECLARE @MAXID INT

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 );

INSERT INTO #fraglist EXEC('DBCC showcontig WITH FAST,tableresults, allindexes,noinfomsgs');

DELETE fl FROM #fraglist fl INNER JOIN sysobjects so (nolock) ON so.id = fl.objectid WHERE so.xtype <> 'U'

DECLARE @TablesIndexes TABLE(id INT IDENTITY(1, 1),objectName VARCHAR(500),indexName VARCHAR(500));

INSERT INTO @TablesIndexes(objectName,indexName) SELECT objectname,indexname FROM #fraglist WHERE logicalfrag >= 10 AND Indexproperty (objectid, indexname,'IndexDepth') >= 0;

SET @ID=1 SET @MAXID=Isnull((SELECT MAX(id) FROM @TablesIndexes), 0)

WHILE @ID <= @MAXID

BEGIN

SELECT @objectName = objectname,@indexName = indexname FROM @TablesIndexes WHERE id = @ID

SET @execstr = 'DBCC DBREINDEX('+RTRIM(@objectName)+','+RTRIM(@indexName)+',0) WITH NO_INFOMSGS;'

EXEC(@execstr);

SET @ID=@ID + 1 END

DROP TABLE #fraglist

GO

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.