asked on

mssql2005 database

Hello,I have a database that I wanted to see how fragmented the indexes are.
I right click on the database then click on reports then standard reports and then index usage statistics but I only got an error incorrect syntax near '(' if I try to use other command to look at the fragmentation of that table I receive the same error. How can I solve this problem and what can be the possible cause of this since the other databases works.

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris M

Here, I have written a script to use to analyse all your fragmented tables and display the extent of fragmentation.

God bless,
Chris Musasizi

-- ANALYSE FRAGMENTATION (NO DEFRAG PERFORMED).
BEGIN
	Declare @dbname sysname, @maxfrag DECIMAL(8,1)
	-- Check if database name has been parsed on calling procedure
	IF @dbname IS NULL SET @dbname = db_name();
	
	-- Declare variables
	SET NOCOUNT ON
	DECLARE @tablename VARCHAR(128), @execstr VARCHAR(255)
	DECLARE @objectid INT, @indexid INT, @frag DECIMAL(8,1)
	
	-- Decide on the maximum fragmentation to allow
	IF (@maxfrag IS NULL OR @maxfrag < 0.0 OR @maxfrag > 5.0) SET @maxfrag = 1.0
	
	-- Declare cursor
	DECLARE cur_tblz CURSOR FOR
	SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
	AND TABLE_NAME NOT IN (SELECT sysobjects.name TableName FROM sysobjects
	WHERE type = 'U' AND OBJECTPROPERTY(sysobjects.id, 'TableHasIndex') = 0)
	
	-- Create the table
	CREATE TABLE ##my_fragmented_table_list(
	   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(9,2),
	   ExtentFrag DECIMAL
	)
	
	-- Open the cursor
	OPEN cur_tblz
	
	-- Loop through all the tables in the database
	FETCH NEXT FROM cur_tblz INTO @tablename
	WHILE @@FETCH_STATUS = 0
	BEGIN
	   -- Do the showcontig of all indexes of the table
	   INSERT INTO ##my_fragmented_table_list 
	   EXEC('DBCC SHOWCONTIG('''+@tablename+''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
	   FETCH NEXT FROM cur_tblz INTO @tablename
	END	
	-- Close and deallocate the cursor
	CLOSE cur_tblz
	DEALLOCATE cur_tblz
	
	IF EXISTS(SELECT * FROM ##my_fragmented_table_list WHERE LogicalFrag >= @maxfrag
		  AND INDEXPROPERTY(ObjectId,IndexName,'IndexDepth') > 0) 
	BEGIN
		SELECT rtrim(ObjectName)+' ' ObjectName, ObjectId, 
			Case when IndexId >= 1 then 'Y' else 'N' end Idx, IndexId, LogicalFrag,
			(select rows from sysindexes where id = (select id from sysobjects 
			 where name = rtrim(##my_fragmented_table_list.ObjectName)) and indid in(0,1))Rows
		FROM ##my_fragmented_table_list
		WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY(ObjectId,IndexName,'IndexDepth')>0
		ORDER BY LogicalFrag DESC, ObjectName ASC
	END
	ELSE PRINT 'No index is fragmented to ' + RTRIM(CONVERT(VARCHAR(11),@maxfrag))+'%'
END