|
|
Posted by Erland Sommarskog on 01/01/08 10:02
David Greenberg (davidgr@iba.org.il) writes:
> Thanks for the link. Problem is that the "dbcc reindex" works on a
> single table , meaning that I have to write a command per table and have
> to remember to add a new line for new tables when I add them.
> Isn't there a single command that will take care of the whole database
> in one go ?
You can easily write a script that iterates over sysobjects to do this.
In this case you can also run DBCC SHOWCONTIG WITH TABLE_RESULTS and
check for fragmentation, so that you only defragment tables that have a
certain fragmentation level.
If you don't like writing code, you can also do:
sp_MSforeachdb 'DBCC DBREINDEX(?)'
The stored procedure sp_MSforeachdb is undocumented and use of it is not
supported. Then again, it's commonly used.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|