Tara Kizer Blog

There are several things that a DBA sets up in a production environment, one of these things is rebuilding the indexes.  This is done with DBCC DBREINDEX.  Some systems can not afford the locks that are held for long periods of time with DBCC DBREINDEX so they use DBCC INDEXDEFRAG instead.  DBCC DBREINDEX still needs to be run occassionally even on these types of systems. 

For systems that have lots of tables, it can be tiresome to write out the DBCC DBREINDEX command for each of the tables.  To make it easier, you can run this to write out the statements for you:

SELECT 'DBCC DBREINDEX([' + u.name + '.' + o.name + '], '''', 80)'
FROM sysobjects o
INNER JOIN sysusers u ON o.uid = u.uid
WHERE o.name <> 'dtproperties' AND o.xtype = 'u'
ORDER BY o.name

The above query accounts for tables that aren't owned by dbo and sets the fillfactor to 80.