I'm putting this out here mostly to find out if I'm doing things 'right' in SQL2K5.

This script is based on Kalen Delaneys` article from SQLMagazine last month (Instant DocID: 47667). There is also a MS Webcast that Kimberly Tripp goes over the same topic.

It's reporting only at the moment, since we don't have a complete (yet) understanding of sys.dm_db_index_physical_stats.

Anybody see anything I should be doing differently? Does SQL2K5 have an alternative to cursors and EXEC statements I should be using? Dynamic SQL is required for this script in order to associate the appropriate object_id with object_name(object_id).

/****** BEGIN SCRIPT ******/

USE Admin
GO

IF EXISTS (SELECT [name] FROM sys.objects WHERE [name] = 'usp_DB_Frag_Detailed' AND TYPE = 'P')
	DROP PROCEDURE usp_DB_Frag_Detailed
GO

CREATE PROCEDURE usp_DB_Frag_Detailed (@Percentage INT = NULL)
						-- Admin.dbo.usp_DB_Frag_Detailed 5

AS
/******************************************************************************
**  	¼ Raylev Database Support & Consulting, 2005.	
** 		This script is provided as is, and it's effects are not liable
**		for any impacts/damage to your systems.
*******************************************************************************
**
**	Name:  usp_DB_Frag_Detailed.sql
**
**	Description:  Report table/index fragmentation (detailed) on all db's.
**
**		      
**
**	Return values:   0 - Success
**					-1 - Error
**
**	Author:  G. Rayburn
**
**	Date:  11/07/2005
**
**	Depends on:  
**  
**
**
*******************************************************************************
**		Modification History
*******************************************************************************
**
**	Initial Creation:  11/07/2005 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON

IF @Percentage IS NULL
	SET @Percentage = 10

DECLARE @DBName VARCHAR(50)
	, @DBID VARCHAR(3)
	, @DynSQL VARCHAR(1000)
	, @Msg VARCHAR(300)
	, @MsgRep VARCHAR(300)

-- Header formatting...
SELECT @Msg = 'Detailed fragmentation report for server: ' + @@SERVERNAME + ' on ' + CONVERT(CHAR(19),getdate(),100) + '.'
PRINT @Msg
SELECT @MsgRep=REPLICATE('=',LEN(@Msg))
PRINT @MsgRep
PRINT ''
PRINT ''
PRINT ''


DECLARE curDBFrag CURSOR 
FOR SELECT [name] 
		FROM master.sys.databases 
		WHERE [name] NOT IN ('model', 'tempdb')
		ORDER BY [name]

OPEN curDBFrag

FETCH NEXT FROM curDBFrag INTO @DBName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		SET @DBID = (SELECT database_id FROM master.sys.databases WHERE [name] = @DBName)

		SET @DynSQL = '

		USE [' + @DBName + ']

		PRINT ''' + @DBName + ':''

		PRINT ''''

		SELECT ISNULL(LEFT(object_name(object_id),40),object_id) AS [ObjName]
			, index_id
			, LEFT(index_type_desc,25) AS [Index Type]
			, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent)) AS [Avg. Frag %]
			, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragment_size_in_pages)) AS [Avg. frag size in pages]
			, fragment_count
			, page_count
			, record_count

		FROM sys.dm_db_index_physical_stats (' + @DBID + ', NULL, NULL, NULL, ''DETAILED'')
		WHERE avg_fragmentation_in_percent > ' + CONVERT(CHAR(2),@Percentage) + '

		ORDER BY CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent)  DESC

		PRINT ''''
		PRINT ''''
		PRINT ''''

		'

		EXEC (@DynSQL);

END
	FETCH NEXT FROM curDBFrag INTO @DBName
END

CLOSE curDBFrag
DEALLOCATE curDBFrag
--
--sp_help 'sys.dm_db_index_physical_stats'
--
GO

/****** END SCRIPT ******/

FWIW, it would be REALLY nice if formatting could be maintained in this forum.

Haywood
I have edited your post and added HTML short forms to maintain formatting... anyway good script too.

---

Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

And here is the associated defrag script (which is subject to change)...

Apologies for the formatting.

Edit 12/28/2005: Added schema_owner to object qualifiers.

/******************************************************************************
**  	¼ Raylev Database Support & Consulting, 2005.	
** 		This script is provided as is, and it's effects are not liable
**		for any impacts/damage to your systems.
*******************************************************************************
**
**	Name:  usp_DB_DeFrag.sql
**
**
**	Description:  Defragment tables/indexes based on output.
**					Based on usp_DB_Frag_Detailed procedure.
**
**	This script can either be run direct and defragment, or it can be run
**	  and the output used as a defrag script.  Simply change EXEC (@DynDeFragList)
**    to PRINT (@DynDeFragList) for script output.
**
**  Added DATABASE DDL TRIGGER disable/enable because the developers are just
**    dying to make my job harder...<sigh>
**
**
**	Author:  G. Rayburn
**	Date:  12/27/2005
**
*******************************************************************************
**		Modification History
*******************************************************************************
**
**	Initial Creation:  12/27/2005 G. Rayburn
**
*******************************************************************************
**
******************************************************************************/
SET NOCOUNT ON
GO

IF EXISTS (SELECT [name] FROM tempdb.sys.objects WHERE [name] LIKE '#_FragList%')
DROP TABLE #_FragList 
GO

-- Disable ddl database triggers.
DISABLE TRIGGER ALL ON DATABASE
GO

DECLARE @Percentage int
	, @DynFragList varchar(1000)
-- Cursor objects:
	, @SchemaName varchar(100)
	, @ObjectName varchar(100)
	, @IndexName varchar(100)
	, @IndexType varchar(25)
	, @AvgFrag int	

SET @Percentage = 10


-- Temp table for later iteration against.
CREATE TABLE #_FragList
		(
		 ObjectName varchar(100)
		,Index_id int
		,IndexType varchar(60)
		,avg_fragmentation_in_percent float
		,avg_fragment_size_in_pages float
		,avg_page_space_used_in_percent float
		,fragment_count bigint
		,page_count bigint
		,record_count bigint
		,forwarded_record_count bigint
		)

		INSERT INTO #_FragList
			SELECT
				  LEFT(object_name(object_id),100)
				, index_id
				, LEFT(index_type_desc,25)
				, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent))
				, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_fragment_size_in_pages))
				, CONVERT(CHAR(9),CONVERT(DECIMAL(9,2),avg_page_space_used_in_percent))
				, fragment_count
				, page_count
				, record_count
				, forwarded_record_count

			FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'DETAILED')
			WHERE avg_fragmentation_in_percent > CONVERT(CHAR(2),@Percentage)
			ORDER BY CONVERT(DECIMAL(9,2),avg_fragmentation_in_percent)  DESC;


-- Do the defrag.
DECLARE curDBFrag CURSOR 
FOR 

	 SELECT  schema_name(so.schema_id) AS [Schema]
			,fl.[ObjectName]
			,si.[name]
			,fl.IndexType
			,fl.avg_fragmentation_in_percent
		FROM #_FragList fl
			, sys.indexes si
			, sys.objects so

		WHERE object_id(fl.ObjectName) = si.object_id
		AND fl.index_id = si.index_id
		AND object_id(fl.objectname) = so.object_id
		AND fl.Index_ID <> 0
		AND si.is_disabled = 0


		GROUP BY so.schema_id 
				,fl.[ObjectName]
				,si.[name]
				,fl.IndexType
				,fl.avg_fragmentation_in_percent

		ORDER BY fl.avg_fragmentation_in_percent DESC

OPEN curDBFrag

FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

		SET @DynFragList = '
PRINT ''-- _.:*~*:._.:*~*:._.:*~*:._.:*~*:._.:*~*:._''
PRINT ''Table: ' + @ObjectName + '
		Index: ' + @IndexName + ' of type: ' + @IndexType + ' is ' + CONVERT(varchar(5),@AvgFrag) + '% fragmented.''

	ALTER INDEX ' + @IndexName + '
			ON ' + @SchemaName + '.' + @ObjectName + ' REORGANIZE;

	PRINT ''''

	DBCC UPDATEUSAGE (''' + db_name() + ''', ''' + @SchemaName + '.' + @ObjectName + ''', '''  + @IndexName + '''); -- WITH NO_INFOMSGS;

	PRINT ''''

	UPDATE STATISTICS ' + @SchemaName + '.' + @ObjectName + ' ' + @IndexName + ';

PRINT ''''
PRINT ''''


'
		PRINT (@DynFragList);

	END
	FETCH NEXT FROM curDBFrag INTO @SchemaName, @ObjectName, @IndexName, @IndexType, @AvgFrag
END

CLOSE curDBFrag
DEALLOCATE curDBFrag;
GO

-- Enable ddl database triggers.
ENABLE TRIGGER ALL ON DATABASE;
GO

Thanks for the formatting again Satya.