Hi
We use Sql 2000.
When defining a database maintenance plan one of the options is to
optimize the database, reorganize data and index pages, remove unused
pages etc.
Is there a command to do this manualy (or in a job) and not by usung a
DBM plan ?

"David Greenberg" <davidgr (AT) iba (DOT) org.il> wrote

Yes. ALL of the features of Maintenance Plans are T-SQL commands.

http://msdn2.microsoft.com/en-us/lib...8(SQL.80).aspx

--
David Portas

David Portas wrote:
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 ?

David Greenberg (davidgr (AT) iba (DOT) org.il) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

You can use the undocumented system stored proc called



Sp_msforeachtable



The parameter is the command you want to run and the proc will execute it
against every table within the database


--
Sincerely,
John K
Knowledgy Consulting, LLC
www.knowledgy.org

Atlanta's Business Intelligence and Knowledge Management Experts


"David Greenberg" <davidgr (AT) iba (DOT) org.il> wrote

Check SQL Books Online and search DBCC SHOWCONTIG - there is a script in
there which is useful and deals
with all the tables

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"David Greenberg" <davidgr (AT) iba (DOT) org.il> wrote