/****** 
 Object: Stored Procedure dbo.sp_dba_DefragIndexes version 2
 Script Author: Robert Davis, robertd@realtechllc.com 
 Purpose: Defrag all indexes in a given table or database.
******/
Use Master
Go

Create Procedure dbo.sp_dba_DefragIndexes
	@Table sysname = Null, -- Table in which to defrag the indexes
	@ShowDetail bit = 0
As

If (Not Exists (Select 1 From sysobjects with(nolock) where id = object_id(@Table) And xtype = 'U') And @Table Is Not Null) Or @Table = '?'
     Begin
	Print '''' + @Table + ''' is not a valid table object in this database.' + char(10)
	Print 'Proper syntax:'
	Print 'Exec dbo.sp_dba_DefragIndexes ' + char(10) + space(5) + '@Table = { [table_name] | ''?'' }' + char(10)
	Print 'table_name'
	Print space(5) + 'Is the table for which to defragment an index. Table names must conform to the rules for identifiers.'
	Print space(5) + 'Table_name is optional, procedure will run for all tables in database if parameter not specified.'
	Print '''?'''
	Print space(5) + 'This help message will be displayed.'
     End
Else
     Begin
	Declare @IndexID int,
		@TableID int,
		@SQL varchar(1000)

	Declare @IndexList Table (TableID int not null, IndexID int not null)

	Insert Into @IndexList (TableID, IndexID)
	Select si.id, si.indid
	From sysindexes si with(nolock)
	Inner Join sysobjects so with(nolock) on so.id = si.id
	Where so.xtype = 'U'   -- User Table
	And so.id = Case When @Table Is Not Null Then object_id(@Table)  -- if null runs for all tables
			Else so.id End
	And si.indid Not In (0, 255)
	And si.rows > 0  -- If 0 rows, then nothing to defrag
	Order By si.id, si.indid

	Declare crsIndex Cursor fast_forward read_only
	For Select TableID, IndexID
	From @IndexList

	Open crsIndex

	Fetch Next From crsIndex Into @TableID, @IndexID

	While @@Fetch_Status = 0
	     Begin
		If @ShowDetail = 0
		    Begin
			Set @SQL = 'DBCC INDEXDEFRAG (0, ' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ') WITH NO_INFOMSGS'
			Exec(@SQL)
		    End
		Else
		    Begin
			Set @SQL = 'DBCC SHOWCONTIG (' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ')'
			Exec(@SQL)
			Set @SQL = 'DBCC INDEXDEFRAG (0, ' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ') WITH NO_INFOMSGS'
			Exec(@SQL)
			Set @SQL = 'DBCC SHOWCONTIG (' + Cast(@TableID as varchar) + ', ' + Cast(@IndexID as varchar) + ')'
			Exec(@SQL)
			Print char(10) + char(10)
		    End
		

		Fetch Next From crsIndex Into @TableID, @IndexID
	     End

	Close crsIndex
	Deallocate crsIndex
     End
GO