CREATE  PROCEDURE sp_dodefrag
AS

/* 	sp_dodefrag Version 1.1
	J.Smith
	01/06/01

	Description :
	This procedure should be run in the context of a specific database.
	It builds a list of all user tables for the database and then uses a cursor
	to step through each table and uses OSQL to run DBCC SHOWCONTIG
	against each tables and insert the results into a temporary table #OSQLOutput
	The output is then parsed to find the Scan Density. If this is below 80% then 
	the table is checked for a clustered index. If one exists it is rebuilt using
	DBCC REINDEX otherwiswe one is created on GEM_DBKEY and then
	dropped. THe rebuilding of the clustered index should help alleviate any 
	fragmentation in the tables data and index pages thus leading to quicker index 
	scans and table scans.

	Revisions
	1.1	Added rowcount to filter out low count tables 	05/06/01
	
*/
set nocount on
--get a list of all user tables in the database
--1.1
--that have more than 300 rows
--below this fragmentation is exagerated
--and has minimal impact on traversing data
--or index pages and extents
--
DECLARE TableList CURSOR FOR 

	select s.name from sysobjects as s
	join sysindexes as i on s.id=i.id
	where s.xtype='U'
	and s.name <> 'dtproperties' 
	and i.indid < 2
	and i.rows >400
	order by s.name
--declare variable to store table name
Declare @table varchar(50)

OPEN TableList 
FETCH NEXT FROM TableList INTO @table
WHILE @@FETCH_STATUS <> -1 
BEGIN 

declare	@SQL varchar(2000),
	@TBID int,
	@ScanDensity numeric(5,2)
	--
	-- get table id required for DBCC SHOWCONTIG
	--
	set @TBID = object_id(@table)
	--
	--create temporary table for storing outputs
	--
	create table #OSQLOutput
	(OutputLine varchar(255) null)
	--
	--build the fragmentation command
	--
	select @SQL = 'dbcc showcontig(' + convert(varchar(10),@TBID) + ')'
	--
	--  Format osql command	
        --
	select @SQL = 'osql /E /d '+ db_name() + ' /S' + @@servername + ' /w512 /Q"' + @SQL + '"'
	--
	insert into #OSQLOutput (OutputLine)
	exec master.dbo.xp_cmdshell @SQL
	--	
	--  Extract results from output
	--
	select @ScanDensity = convert(numeric(15,2),
	substring(OutputLine,charindex('.:',OutputLine) + 2,
	charindex('%',OutputLine) - charindex('.:',OutputLine)-2))
	from #OSQLOutput
	where OutputLine like '%Scan Density _Best Count:Actual Count_%'
	--
	--clear temporary table
	--
	drop table #OSQLOutput
	--
	--Check defrag level
	--
	If @ScanDensity < 80.00
	begin	
	print @table + ' requires defragmenting because of Scan Density of '+ cast(@ScanDensity as varchar)
	--Check for existing clustered index
	if exists (select id from dbo.sysindexes where id = object_id(@table) and indid=1)
	begin
	DBCC DBREINDEX(@table) with NO_INFOMSGS
	end
	else
	--Otherwise create one on GEM_DBKEY and then drop it
	begin
	print 'Creating clustered index on GEM_DBKEY'
	declare @indexstr varchar(255), @dropstr varchar(255)
	set @indexstr=('CREATE CLUSTERED INDEX ZZ ON ' + @table + '(GEM_DBKEY) WITH FILLFACTOR=90')
	exec(@indexstr)
	DBCC DBREINDEX(@table) with NO_INFOMSGS
	set @dropstr=('DROP INDEX ' + @table + '.ZZ')
	exec(@dropstr)
	end
	end
	--else
	--print @table + ' does not require defragmenting at this time'
	
   FETCH NEXT FROM TableList INTO @table
END
--Cleanup objects
CLOSE TableList
DEALLOCATE TableList

quote:

---

There's a great script for this written specifically for 6.5 here as well.

http://www.mssqlserver.com/scripts/sp__defrag.sql

Jeff Banschbach
Consultant, MCDBA

---

Thanks Jeff, I should have thought to look on Mike's site, It's always loaded with good stuff.