/******
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
