Database maintenance using DBCC DBREINDEX

Hi,
Currently, I use sp_msForEachTable to reindex every table in one of my
databases. However I did not find any information regarding this stored
procedure in BOL. Is it safe to use, or why isn't it documented?
EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
Yours sincerely,
Jo Segers.
Hi,
The procedure sp_MSforeachtable executes a set of given commands against all
the user tables in the current database.
Have a look into this link for detailed information and usage.
http://www.bstsoftware.com/tsug/Nov9...eachtable.html
Thanks
Hari
MCDBA
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Adding to the other posts:
That procedure only contains a cursor which loops all tables in the database and executes the specified
command against the tables. You can easily write such a cursor yourself. The procedure is likely to work the
same way in future service packs of SQL2K, and when Yukon comes, you want to look at these things anyhow.
Still, it is not documented, not supported, use at own "risk", which only you can assess.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Thanks,
Do you know if there is a similar stored procedure that is supported?
Jo.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hi Jo,
> The command sp_msForEachTable is no longer supported my
> Microsoft. This means on the next release of SQL Server it
> may or may not be part of it.
> If its not supported then there are no BOL's for it.
> J
>
> in one of my
> regarding this stored
> documented?
|||I'm sure there exists such on the Net, but if they are free, I doubt that you will have formal support form
the one who wrote it. AFAIK, there is no such in SQL Server. But, again, it is not difficult to write one
yourself, using a cursor, if you have a little bit if TSQL programming experience.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jo Segers" <segers_jo@.hotmail.com> wrote in message news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
> Thanks,
> Do you know if there is a similar stored procedure that is supported?
> Jo.
> "Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
> news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
>
|||Ok,
Thanks for the information, I will write one myself. I wonder why they
didn't implement this sp in SQLserver 2000 because it is quite usefull.
Thanks for the support,
Jo.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> schreef
in bericht news:uYzs9B5JEHA.208@.tk2msftngp13.phx.gbl...
> I'm sure there exists such on the Net, but if they are free, I doubt that
you will have formal support form
> the one who wrote it. AFAIK, there is no such in SQL Server. But, again,
it is not difficult to write one
> yourself, using a cursor, if you have a little bit if TSQL programming
experience.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:%23lKZD$4JEHA.2624@.TK2MSFTNGP09.phx.gbl...
>
|||Hi Jo,
If you want a way to run a command against all fragmented indexes in a
databases, check out example E in the Books Online for DBCC SHOWCONTIG. You
should not use sp_msForEachTable.
I'd like to go to the root of your problem, which is wanting to reindex all
the tables in one of your database. I take it you're doing this for
performance reasons. The only way you'll increase performance by doing this
is if the index (clustered or non-clustered) has logical fragmentation and
is used for a range scan. I'm guessing that not all the indexes you're
rebuilding are used in this fashion. Also, sometimes it can be better to run
DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
I recommend you read the whitepaper on this topic at
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Let me know if you have any more questions.
Regards
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jo Segers" <segers_jo@.hotmail.com> wrote in message
news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Currently, I use sp_msForEachTable to reindex every table in one of my
> databases. However I did not find any information regarding this stored
> procedure in BOL. Is it safe to use, or why isn't it documented?
> EXEC sp_msForEachTable @.COMMAND1='DBCC DBREINDEX ("?")'
> Yours sincerely,
> Jo Segers.
>
|||Hi Paul,
I'm still reading the whitepaper, but the example in BOL helped a lot. I am
going to adjust the code in the example for use in my produktion database.
I am just curious: why isn't sp_msForEachTable supported anymore?
Yours sincerely,
Jo Segers.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> schreef in bericht
news:O2t8tk5JEHA.2376@.tk2msftngp13.phx.gbl...
> Hi Jo,
> If you want a way to run a command against all fragmented indexes in a
> databases, check out example E in the Books Online for DBCC SHOWCONTIG.
You
> should not use sp_msForEachTable.
> I'd like to go to the root of your problem, which is wanting to reindex
all
> the tables in one of your database. I take it you're doing this for
> performance reasons. The only way you'll increase performance by doing
this
> is if the index (clustered or non-clustered) has logical fragmentation and
> is used for a range scan. I'm guessing that not all the indexes you're
> rebuilding are used in this fashion. Also, sometimes it can be better to
run
> DBCC INDEXDEFRAG instead of DBCC DBREINDEX.
> I recommend you read the whitepaper on this topic at
>
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
> Let me know if you have any more questions.
> Regards
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jo Segers" <segers_jo@.hotmail.com> wrote in message
> news:uh22kT4JEHA.4052@.TK2MSFTNGP11.phx.gbl...
>
|||As far as I know there is no Microsoft official version
that does the same, sorry.
However you can get all the user tables from your database
by doing
select Name from sysobjects where xtype = 'U'
J

>--Original Message--
>Thanks,
>Do you know if there is a similar stored procedure that
is supported?
>Jo.
>"Julie" <anonymous@.discussions.microsoft.com> schreef in
bericht[vbcol=seagreen]
>news:201f01c42789$52e4ddb0$a601280a@.phx.gbl...
it[vbcol=seagreen]
table
>
>.
>
|||Thanks for the help.
"Julie" <anonymous@.discussions.microsoft.com> schreef in bericht
news:216f01c427a7$9b1e6a60$a601280a@.phx.gbl...[vbcol=seagreen]
> As far as I know there is no Microsoft official version
> that does the same, sorry.
> However you can get all the user tables from your database
> by doing
> select Name from sysobjects where xtype = 'U'
> J
>
> is supported?
> bericht
> it
> table