For SQL 2000. Scan frag. in all system indexes

/*
**  Author: Rodrigo Acosta
**  Email: acosta_rodrigo@hotmail.com
**
**  Script to be included in a Job that manages fragmentation
**  in all system indexes in the executing database.
*/

Set Nocount On

/*
** *************Error Message Definition********************
*/
/* Declare Variables that wil be used for the error message */Declare @Error_no Int
Declare @AddMessage Varchar(1500)

/* Calculates the last error number of the message */Set @Error_no = (Select Top 1 error From master.dbo.sysmessages Order by error Desc)

/*
   First checks if the message that will be added exists.
   If it doesn't, calculates the available error number 
   and creates the message.
*/If Not Exists
(Select description from master.dbo.sysmessages
Where description Like ('%View C:\Frag_Info.log for more info.'))
Begin
/* 
   If no user define error messages were added (Error < 50001).
   Creates the message with the number 50001.
*/If @Error_no < 50001
Begin
Set @Error_no = 50001
Set @AddMessage = 'EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ', 
   @severity=19,
   @msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
   @with_log=True'
EXEC (@AddMessage)
End
Else
/*
   If there is an used define error (Error > 50001). Calculates the
   first available error number and creates the error message.
*/Begin
Set @Error_no = @Error_no + 1
Set @AddMessage='EXEC Sp_addmessage @msgnum=' + Convert(VarChar(5), @Error_no) + ', 
   @severity=19,
   @msgtext="%s index %s on table %s is %s percent fragmented. Consider re-building the index. View C:\Frag_Info.log for more info.",
   @with_log=True'
EXEC (@AddMessage)
End
End

/*
** *************Alert Definition********************
*/
/*
** If the Alerts Does not exists, it is created to response to the Error message 
** created before.
*/If Not Exists
(Select Name From msdb.dbo.sysalerts Where name = 'Fragmentation above 50% in user index.')
Begin
EXECUTE msdb.dbo.sp_add_alert @name = 'Fragmentation above 50% in user index.',
@message_id = @Error_no,
@Severity = 0,
@Enabled = 1,
@delay_between_responses = 0,
@category_name = '[Uncategorized]'
End


/*
** *************Selecting Sysindexes columns********************
*/

/*  Creates a Temp Table to hold the results from DBCC ShowContig   */
/*  Creates a Temp Table to show the results from DBCC ShowContig  */Create Table #SaveResults
(ObjectName Sysname,
ObjectId int,
IndexName Sysname,
Indexid tinyint,
[Level] int,
Pages Int,
[Rows] Int,
MinimumRecordSize Int,
MaximumRecordSize Int,
AverageRecordSize Int,
ForwardedRecords Int,
Extents Int,
ExtentSwitches Int,
AverageFreeBytes Int,
AveragePageDensity Int,
ScanDensity Int,
BestCount tinyint,
ActualCount TinyInt,
LogFragmentation TinyInt,
ExtentFragmentation Int)

/*  Creates a Temp Table to show the modified results from DBCC ShowContig   */Create Table ##ShowResults
(TableName sysname,
IndexName sysname,
IndexType Varchar (12),
[%Frag] TinyInt,
Defrag VarChar (3),
Pages Int,
AvgFreeBytes Int)

/* 
   Declares a cursor and variables that holds the table and index Type  (Clustered or Nonclustered) 
   and the FillFactor (original) from user tables with indexes.
*/   
Declare @TableName sysname
Declare @IndexName sysname
Declare vCursor Cursor For
Select Object_name (i.id) As 'TableName',
i.name As 'IndexName' 
From Sysindexes i Inner Join sysobjects o
On i.id=o.id
Where (Indid = 1 Or Indid Between 2 And 250) And xtype = 'S'

Declare @cmdDBCC Varchar (200)


Open vCursor
Fetch Next From vCursor Into @TableName, @IndexName
While @@Fetch_Status = 0
Begin
Set @cmdDBCC = 'DBCC ShowContig (' + @TableName + ', ' + @IndexName + 
') With TableResults'
Insert #SaveResults
Exec (@cmdDBCC)
/*  Fills the #SaveResults Table with all the results of the execution*/Fetch Next From vCursor Into @TableName, @IndexName
End

/*  Close the cursor because it don't use it anymore   */Close vCursor
Deallocate Vcursor

/*  Declare variables to save info from the #SaveResults   */Declare @ObjectName Sysname  
Declare @IndexName2 Sysname
Declare @ScanDensity Int
Declare @IndexType Varchar(13)
Declare @Pages  Int
Declare @AverageFreeBytes Int
Declare @Rows Int
Declare @Defrag Char(3)
Declare vCursor2 Cursor For 
Select ObjectName, IndexName, ScanDensity, 
[IndexType]=
Case
When IndexId = 1 Then 'Clustered'
Else 'NonClustered'
End
, 
Pages, [Rows], AverageFreeBytes
From #SaveResults
Open vCursor2
Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType, 
@Pages, @Rows, @AverageFreeBytes

While @@Fetch_Status = 0
Begin
/* If fragmentation is above 50 % Then fire the error */If @ScanDensity < 50 
Begin
/* Builts the raise error sentence  */Declare @Raise Varchar(150)
Set @Defrag = 'Yes'
Set @Raise = 'Raiserror (' + Convert(Varchar(10),@Error_no) +
', 18, 1,' + ''''  + @IndexType + '''' + ',' + '''' +  @IndexName2+ '''' + ',' + '''' +
 @ObjectName + '''' + ',' + '''' + Convert(Varchar(20),@ScanDensity) + '''' + ')'
EXEC (@Raise)
End
/*  If not just alter the variable  to indicate no defragmentation is needed*/Else
Begin
Set @Defrag = 'No'
End

/*  Insert the results into the temp table created to show the results  */Insert ##ShowResults
Values
(@ObjectName, @IndexName2, @IndexType, 
@ScanDensity, @Defrag, @Pages, 
@AverageFreeBytes)

Fetch Next From vCursor2 Into @ObjectName, @IndexName2, @ScanDensity, @IndexType, 
@Pages, @Rows, @AverageFreeBytes
End

Close vCursor2
Deallocate vCursor2

Drop Table #SaveResults

/*
** *************Log File generation********************
*/Declare @cmd Varchar(1000)
Set @cmd=' EXEC master.dbo.xp_cmdShell ' + '''' + 
'OSQL -E -q"Set Nocount On Select Substring(@@ServerName,1,20) as [Executed On Server:] Select Substring(db_name(),1,20) ' + 
'AS [Executed On Database:] Select Getdate() as [LogFile generated with the ' + 
'results of fragmentation in all user Indexes. Date:] Select ' + 
'SubString(TableName,1,20) as TableName,IndexType, Substring(IndexName,1,20) ' + 
'as IndexName, [%Frag] as [%Defrag] , Defrag As [Need Defrag.?]  from ##ShowResults" -oC:\Frag_Info.log' + ''''
EXEC (@cmd)
Drop Table ##ShowResults