Managing Table Fragmentation - Sql Server 2000

Performance.... it's an intangible and it seems that no matter how fast and how optimized you set your system up, it seems that there is always new information telling you what you should be doing as well. It seems that there really is no end on to what you can do with software, and when you think you've reached the end of that intangible, then there are all the sorts of things you can do in the hardware world to further push the performance limits. Don't get me wrong, I dig being able to find these tidbits... these jewels of information. It just seems sometimes that you've completed implementing something when now new information, could have helped you out before you started planning. So, What do you do when you continue to manage more and more Sql Servers in your environment? I started out with 8 unhealthy servers. I kept on them and have been able to kick the developers square in the butt!* Part of the butt kicking allowed me to set new rules in place. I've managed to get my developers to work their ideas on their own TEST servers, well server is a very liberal term, we have Sql Server Dev edition running on a separate desktop system under their desk, and does allow them to perform similar performing queries against similar data, if they require more data, I simply restore a copy of the db to their test environment.

*disclaimer: no actual butts were kicked in the actual story, the emphasis on kicking is written to enhance the fictional portion of the story.

/*
Object: Stored Procedure dbo.sp_dba_DBREINDEX version 1
Script Author: Francisco Tapia,
Purpose: REINDEX all indexes in a given table or database.

Based off the work from Robert Davis http://www.databasejournal.com/img/sp_dba_DefragIndexes.sql


*/
USE MASTER
if exists (select * from sysobjects where id =
object_id('dbo.sp_dba_DBREINDEX') and sysstat & 0xf = 4)
drop procedure dbo.sp_dba_DBREINDEX

GO
CREATE PROCEDURE sp_dba_DBREINDEX
(
@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_DBREINDEX ' + 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


If @ShowDetail = 0
BEGIN
IF @Table IS NULL
BEGIN
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '''', 0) WITH NO_INFOMSGS'
END
ELSE
BEGIN
DBCC DBREINDEX (@Table, '', 0) WITH NO_INFOMSGS
END
END
Else
BEGIN
IF @Table IS NULL
BEGIN
PRINT 'Initiating DBReIndex for All Tables'
EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '''', 0) '
END
ELSE
BEGIN
PRINT 'Initiating DBReIndex for ' + @Table
DBCC DBREINDEX (@Table, '', 0)
END
END

END