DBCC InputBuffer statement to be part of a select statement

I want to use DBCC inputbuffer function for all the spids in sysprocesses table without using a cursor or loop. In other words, I want to have the value of dbcc inputbuffer part of the select statement directly or indirectly. Is there a way??SQLDBAxxx,
Try running the following:

----------
set nocount on
select 'exec DBCC INPUTBUFFER('+convert(varchar,spid)+')'
from sysprocesses
go
----------

This will generate the DBCC code you need to run; it won't run
DBCC in the TSQL mode that you need. You can save the result set
and reload it to run it. Simple, but works...

I've never been able to execute a DBCC statement using ANSI-SQL syntax( select,update, insert ).|||SQLDBAxxx,
Try running the following:

----------
set nocount on
select 'DBCC INPUTBUFFER('+convert(varchar,spid)+')'
from sysprocesses
go
----------

This will generate the DBCC code you need to run; it won't run
DBCC in the TSQL mode that you need. You can save the result set
and reload it to run it. Simple, but works...

I've never been able to execute a DBCC statement using ANSI-SQL syntax( select,update, insert ).|||Thank you Scooter. But this is not what I wanted to do. I want to capture the input buffer for a given spid. I can do it thro a cursor or loop but if my recordset is big then by the time it comes to execute the dbcc inputbuffer for the last spid the inputbuffer may have changed. That is the reason I wanted to try by some other way to narrow down the time difference.

dbcc dbreindex on all tables/indexes and slow

This is what i ran and it was slow
declare reindex_cursor insensitive cursor for
select TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
and table_type = 'Base table'
declare @.table_name sysname
open reindex_cursor
fetch next from reindex_cursor into @.table_name
while (@.@.fetch_status = 0)
begin
print ('dbcc dbreindex(' + @.table_name + ')' )
exec ('dbcc dbreindex(' + @.table_name + ')' )
fetch next from reindex_cursor into @.table_name
end
close reindex_cursor
deallocate reindex_cursor
Could the problem be because of the insensitive that i mentioned in the
cursor declaration ? I can see that the DBCC is still running and not being
blocked but its way too slow. If I do cancel in between, will it rollback
everything ?For something like that I always use a STATIC cursor. You also don't need
to use dynamic sql as DBCC DBREINDEX will take a variable for the table
name. My guess why the operation is so slow is that your log and db files
probably don't have enough room in them for this and are constantly growing.
If your in full recovery mode you might want to backup your logs in between
so try something like this:
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
--
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Why you don't use an database maintance plan?
Ronald
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Now changing gears to the use of dynamic sql. How can i tell what needs a
dynamic sql and what can accept variables. Can you provide some examples and
how can i figure it out ? Also the definition of insensitive and static
seemed to be the same from BOL. Btw, i got the initial script from google
too.
Thanks
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> For something like that I always use a STATIC cursor. You also don't need
> to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> name. My guess why the operation is so slow is that your log and db files
> probably don't have enough room in them for this and are constantly
growing.
> If your in full recovery mode you might want to backup your logs in
between
> so try something like this:
>
> SET NOCOUNT ON
> DECLARE @.TableName VARCHAR(100), @.Counter INT
> SET @.Counter = 1
> DECLARE curTables CURSOR STATIC LOCAL
> FOR
> SELECT Table_Name
> FROM Information_Schema.Tables
> WHERE Table_Type = 'BASE TABLE'
> OPEN curTables
> FETCH NEXT FROM curTables INTO @.TableName
> SET @.TableName = RTRIM(@.TableName)
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SELECT 'Reindexing ' + @.TableName
> DBCC DBREINDEX (@.TableName)
> SET @.Counter = @.Counter + 1
> -- Backup the Log every so often so as not to fill the log
> IF @.Counter % 10 = 0
> BEGIN
> BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
NOUNLOAD
> ,
> NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> NOFORMAT
> END
> FETCH NEXT FROM curTables INTO @.TableName
> END
> CLOSE curTables
> DEALLOCATE curTables
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>|||Sorry, I spaced on the Insensitive. As for when you can and when you can't
it isn't always spelled out but if you look at the entry in BOL it usually
gives a hint. If there are quotes around the option you can usually use a
variable.
DBCC DBREINDEX
( [ 'database.owner.table_name'
[ , index_name
[ , fillfactor ]
]
]
) [ WITH NO_INFOMSGS ]
Andrew J. Kelly
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23CQ4liTsDHA.3416@.tk2msftngp13.phx.gbl...
> Now changing gears to the use of dynamic sql. How can i tell what needs a
> dynamic sql and what can accept variables. Can you provide some examples
and
> how can i figure it out ? Also the definition of insensitive and static
> seemed to be the same from BOL. Btw, i got the initial script from google
> too.
> Thanks
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uXF66WQsDHA.4088@.TK2MSFTNGP11.phx.gbl...
> > For something like that I always use a STATIC cursor. You also don't
need
> > to use dynamic sql as DBCC DBREINDEX will take a variable for the table
> > name. My guess why the operation is so slow is that your log and db
files
> > probably don't have enough room in them for this and are constantly
> growing.
> > If your in full recovery mode you might want to backup your logs in
> between
> > so try something like this:
> >
> >
> > SET NOCOUNT ON
> >
> > DECLARE @.TableName VARCHAR(100), @.Counter INT
> >
> > SET @.Counter = 1
> >
> > DECLARE curTables CURSOR STATIC LOCAL
> > FOR
> > SELECT Table_Name
> > FROM Information_Schema.Tables
> > WHERE Table_Type = 'BASE TABLE'
> >
> > OPEN curTables
> > FETCH NEXT FROM curTables INTO @.TableName
> > SET @.TableName = RTRIM(@.TableName)
> >
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > SELECT 'Reindexing ' + @.TableName
> >
> > DBCC DBREINDEX (@.TableName)
> >
> > SET @.Counter = @.Counter + 1
> > -- Backup the Log every so often so as not to fill the log
> > IF @.Counter % 10 = 0
> > BEGIN
> > BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT ,
> NOUNLOAD
> > ,
> > NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
> > NOFORMAT
> > END
> >
> > FETCH NEXT FROM curTables INTO @.TableName
> > END
> >
> > CLOSE curTables
> > DEALLOCATE curTables
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:%23iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > > This is what i ran and it was slow
> > >
> > > declare reindex_cursor insensitive cursor for
> > > select TABLE_NAME
> > > from INFORMATION_SCHEMA.TABLES
> > > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > > and table_type = 'Base table'
> > >
> > > declare @.table_name sysname
> > >
> > > open reindex_cursor
> > > fetch next from reindex_cursor into @.table_name
> > >
> > > while (@.@.fetch_status = 0)
> > > begin
> > > print ('dbcc dbreindex(' + @.table_name + ')' )
> > > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > > fetch next from reindex_cursor into @.table_name
> > > end
> > >
> > > close reindex_cursor
> > > deallocate reindex_cursor
> > >
> > > Could the problem be because of the insensitive that i mentioned in
the
> > > cursor declaration ? I can see that the DBCC is still running and not
> > being
> > > blocked but its way too slow. If I do cancel in between, will it
> rollback
> > > everything ?
> > >
> > >
> >
> >
>|||On a seperate note, I'd like to encourage you to read the excellent
whitepaper at
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
It will help you determine the best way to deal with fragmentation and
whetehr you need to do anything about it at all, rather than just rebuilding
every table in the database.
Regards,
Paul.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> This is what i ran and it was slow
> declare reindex_cursor insensitive cursor for
> select TABLE_NAME
> from INFORMATION_SCHEMA.TABLES
> where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> and table_type = 'Base table'
> declare @.table_name sysname
> open reindex_cursor
> fetch next from reindex_cursor into @.table_name
> while (@.@.fetch_status = 0)
> begin
> print ('dbcc dbreindex(' + @.table_name + ')' )
> exec ('dbcc dbreindex(' + @.table_name + ')' )
> fetch next from reindex_cursor into @.table_name
> end
> close reindex_cursor
> deallocate reindex_cursor
> Could the problem be because of the insensitive that i mentioned in the
> cursor declaration ? I can see that the DBCC is still running and not
being
> blocked but its way too slow. If I do cancel in between, will it rollback
> everything ?
>|||Thanks Paul, I should have included that again for completeness but I know
that link has been referred to him several times already<g>.
--
Andrew J. Kelly
SQL Server MVP
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:uwswsAisDHA.2244@.TK2MSFTNGP09.phx.gbl...
> On a seperate note, I'd like to encourage you to read the excellent
> whitepaper at
>
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
> It will help you determine the best way to deal with fragmentation and
> whetehr you need to do anything about it at all, rather than just
rebuilding
> every table in the database.
> Regards,
> Paul.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:#iFKndOsDHA.2252@.TK2MSFTNGP09.phx.gbl...
> > This is what i ran and it was slow
> >
> > declare reindex_cursor insensitive cursor for
> > select TABLE_NAME
> > from INFORMATION_SCHEMA.TABLES
> > where objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0
> > and table_type = 'Base table'
> >
> > declare @.table_name sysname
> >
> > open reindex_cursor
> > fetch next from reindex_cursor into @.table_name
> >
> > while (@.@.fetch_status = 0)
> > begin
> > print ('dbcc dbreindex(' + @.table_name + ')' )
> > exec ('dbcc dbreindex(' + @.table_name + ')' )
> > fetch next from reindex_cursor into @.table_name
> > end
> >
> > close reindex_cursor
> > deallocate reindex_cursor
> >
> > Could the problem be because of the insensitive that i mentioned in the
> > cursor declaration ? I can see that the DBCC is still running and not
> being
> > blocked but its way too slow. If I do cancel in between, will it
rollback
> > everything ?
> >
> >
>

dbcc dbreindex

Hi All,

I have a following script:

DECLARE @.TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Rebuilding indexes ' + @.TableName + ' table'
DBCC DBREINDEX (@.TableName, ' ', 10)
FETCH NEXT FROM cur_reindex INTO @.TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO

I added as a step in the job. However, when I run it I get an error message:

Msg 2501, Sev 16: Could not find a table or object named 'table_1'. Check sysobjects. [SQLSTATE 42S02]

When I run
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'

table_1 is the first on list. Any idea why I am getting this error message?Hi Inka

I almost replied to your previous post with this but didn't bother. I doubt you'll produce something as good as Tara's (http://weblogs.sqlteam.com/tarad/) script (scroll down and you'll see her sproc - there are more db maintenance ones too). As a minimum you should get some good pointers.

HTH|||I would bet that dbo does not own table_1.|||MCrowley,

You are right. It is user1 that owns that table. How can I fix the script so I can prefix the table name with user1?|||Concatenate with TABLE_SCHEMA + '.' field.|||Thank you very much for your help. It worked.

DBCC DBREINDEX

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
See the reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Dave Mortenson" <dmortenson@.dentrix.com> wrote in message
news:enpAF$obEHA.1356@.TK2MSFTNGP09.phx.gbl...
> 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
>

DBCC DBREINDEX

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 ReindexTableCursorSee the reply in the other newsgroup.
--
Andrew J. Kelly SQL MVP
"Dave Mortenson" <dmortenson@.dentrix.com> wrote in message
news:enpAF$obEHA.1356@.TK2MSFTNGP09.phx.gbl...
> 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
>

DBCC DBREINDEX

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 ReindexTableCursorSee the reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Dave Mortenson" <dmortenson@.dentrix.com> wrote in message
news:enpAF$obEHA.1356@.TK2MSFTNGP09.phx.gbl...
> 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
>