Can anyone confirm this for me. If I were to run this script below. Will it
reindex all tables indexes at one time as it goes through the cursor. Or
will it reindex one table indexes at a time as it goes through the cursor.
Basically will it pause each time the DBCC command is executed?
If the first part is true where it executes all at once then what is a good
way to only do one at a time? Can you pause between executions till each
execution is done?

THanks for the help.

DECLARE @TableName varchar(255)
DECLARE ReindexTableCursor CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'U' AND (name = 'Practice' OR name
= 'ID_Master')
ORDER BY name

OPEN ReindexTableCursor

-- Perform the first fetch.
FETCH NEXT FROM ReindexTableCursor INTO @TableName

-- Check @@FETCH_STATUS to see if there are any rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
PRINT 'Reindexing' + ' ' + @TableName
DBCC DBREINDEX (@TableName )
FETCH NEXT FROM ReindexTableCursor into @TableName
END

CLOSE ReindexTableCursor
DEALLOCATE ReindexTableCursor

Dave,

If you execute DBCC DBREINDEX with only the table specified it will rebuild
ALL indexes on that table. This happens all in one big transaction for each
table. You can specify a single index to be rebuilt but as you have it here
it will rebuild all of them on each table in the cursor. You can pause in
between each table by placing a WAITFOR DELAY after the DBREINDEX command.
You really should look at using one of the information schema views as the
source of the cursor instead of the system tables directly.
INFORMATION_SCHEMA.TABLES



--
Andrew J. Kelly SQL MVP


"Dave Mortenson" <dmortenson (AT) dentrix (DOT) com> wrote

OK Thanks for the information. I will use INFORMATION_SCHEMA.TABLES. I do
understand if I only specify the table name it will reindex all indexes in
table thius is what I am after. Although what I was wondering is if I use
this script and my query returns 15 table names lets say will it execute the
dbcc command on all 15 tables at the same time or will it execute the dbcc
command one at a time as it sequencially goes through the cursor. I only
want to reindex one table and all it's indexes at a time. Can you tell me if
this is true?


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Dave,

The whole point of a Cursor is to loop thru the result set one row at a
time. The cursor will only return 1 table name with each loop and since
your specifying the table name via the variable that gets filled in thru the
cursor, DBCC DBREINDEX will only work on that individual table at that time.
As a matter of fact there is no way to reindex all tables or indexes all at
once with one command. Hope that helps.

--
Andrew J. Kelly SQL MVP


"Dave Mortenson" <dmortenson (AT) dentrix (DOT) com> wrote

This is true. What I was trying to get at was in a query analyzer window it
takes only a few seconds to query and return all my tables. Will the cursor
also loop through all the tables in a few seconds and issue multiple dbcc
commands all at once. Or will it loop once issue the dbcc dbreindex command
wait for it to finish then loop again to the next and so on this way it wont
issue multiple commands all at once? This is what I was wondering if I
needed to pause in between loops.

THanks Andrew.


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

OK, I see where you are going now. TSQL is a procedural language and can
not by itself run multiple paths or commands per say. Before the next
command can be executed the one before it must finish completely. This
includes commands inside a cursor. The next fetch operation will not occur
before the previous DBCC command is completed. So in that regard you don't
need a WAITFOR. Sometimes people want the system to take a breather in
between tables since this can be a very intensive operation.
Now this does not mean multiple users can not be doing stuff at the same
time. Each user has it's own threads that run independently of each other.
But within a single batch of TSQL it is essentially procedural.


--
Andrew J. Kelly SQL MVP


"Dave Mortenson" <dmortenson (AT) dentrix (DOT) com> wrote

Andrew Excellent that is what I wanted to know. Thank you so much. Do you
know of a way to make it print as it completes each execution rather than
all at the end? I do use the print command but like I said it will only
print all at the end.

THanks again I really appreciate the info.

Dave


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

You could get rid of your cursor by using sp_MSforeachtable

See notes below from web site re: undocumented SP's:
http://www.mssqlcity.com/Articles/Un...000UndocSP.htm
sp_MSforeachtable
Sometimes, you need to perform the same actions for all tables in the
database. You can create cursor for this purpose, or you can also use the
sp_MSforeachtable stored procedure to accomplish the same goal with less
work.

For example, you can use the sp_MSforeachtable stored procedure to rebuild
all the indexes in a database:

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"


"Dave Mortenson" <dmortenson (AT) dentrix (DOT) com> wrote

That is due to the way it buffers the output and there is no easy way to do
what your asking that I know of. It is something that a lot of people ask
about but I haven't seen a good solution.

--
Andrew J. Kelly SQL MVP


"Dave Mortenson" <dmortenson (AT) dentrix (DOT) com> wrote

sp_MSforeachtable uses a cursor to achieve this so your not really getting
rid of the cursor.


--
Andrew J. Kelly SQL MVP


"Joe Horton" <horj235 at lni dot wa dot gov> wrote