Update: Try here for a script to run DBCC DBREINDEX on all indexes on all tables in a database: http://www.mssqlcity.com/Articles/Adm/index_fragmentation.htm
Here's the one line to do a DBREINDEX on all indexes in a databases. It uses an unofficial SQL Server function. Also, remember to do this during down time! This LOCKS tables.
USE xxx;
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)";
Sometimes, you'll want to SHRINK the DB after the DBREINDEX:
DBCC SHRINKDATABASE (UserDB, 10);
Loop all user tables in SQL Server, optimize all indexes, etc.
Some other good TSQL scripts and stuff:
http://www.mssqlcity.com/Scripts/scrMaint.htm
There is a great script in BOL for 2000, that will do the online defrag of only indexes which are above a threshold in fragmentation.
Comparison of DBCC DBREINDEX to DBCC INDEXDEFRAG:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
"DBCC INDEXDEFRAG does not help to untangle indexes that have become interleaved within a data file. Likewise, DBCC INDEXDEFRAG does not correct extent fragmentation on indexes. Interleaving occurs when index extents (a group of eight index pages) for an index are not completely contiguous within the data file, leaving extents from one or more indexes intermingled in the file. Interleaving can occur even when there is no logical fragmentation, because all index pages are not necessarily contiguous, even when logical ordering matches physical ordering."
Reindex one table: DBCC DBREINDEX ('HumanResources.Employee', ' ', 90);
"DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table." -- I think this is referred to as "Rebuild Index" in newer SQL Server Maintenance Plan Wizards.
tags: script, optimize, reindex, mssql, defrag, defragment, logical, extent, frags, fragment, fragments, rebuild, index, compact, shrink