Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Reindex All Tables in All Databases

Author  Topic 

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-11 : 14:14:13
I am creating some scripts to ease the transition as we transfer databases from SQL 7 to SQL 2000. One of the recommended steps is to run DBCC DBREINDEX on all tables in the database. Thanks to Ken Henderson's The Guru's Guide to Transact-SQL Chapter 20: Undocumented Procedures, I have been working with sp_MSforeachtable, and now have the following code that will reindex all tables within the current database:

EXEC sp_MSforeachtable @command1='DBCC DBREINDEX (''*'')', @replacechar='*'

So far, so good. Now, I tried to combine that with the sp_MSforeachdb procedure like this:

EXEC sp_MSforeachdb '
EXEC sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'',
@replacechar=''*'''


and I end up with a series of error messages that all say:
Server: Msg 16915, Level 16, State 1, Line 1
A cursor with the name 'hCForEach' already exists.

Any ideas on getting this to work? Is it possible?

TIA,

NOTE: I'm using the replacechar='*' so that the replacement char for DB's does not conflict with the one for tables. Not sure if this is necessary, but it sounded good.

------------------------
GENERAL-ly speaking...

ToddV
Posting Yak Master

218 Posts

Posted - 2002-03-11 : 14:27:55
Mark,
I took a look at these procedures. They are all tied up together. They both call sp_MSforeach_worker which is looking for a global cursor named hCForEach. More work than worth to work around. I would just write the database cursor and call msforeachtable or as long as you knew you did not have too many databases to approach 4000 character limit. You could do this:


DECLARE @SQL NVarchar(4000)
SET @SQL = ''

SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases

PRINT @SQL
EXEC (@SQL)




Edited by - toddv on 03/11/2002 14:29:45
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-11 : 14:29:05
Unfortunately the two sp_MSforeach... procedures use the same internal cursor, so you can't call one within the other. You'll have to create an outer database "cursor" to loop through each database, then construct some dynamic SQL to incorporate the sp_MSforeachtable and execute it.

I believe Ken mentions this is one of his books, it might be the 2nd one (can't find it now, of course, but I know I saw it somewhere).

Man, the snipers around here are fierce!

Edited by - robvolk on 03/11/2002 14:30:01
Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-11 : 16:21:59
quote:

Unfortunately the two sp_MSforeach... procedures use the same internal cursor, so you can't call one within the other.


<rant>
Well whose bright idea was that?! Oh sure, I know, code reusability... but surely I'm not the first guy who thought nesting these could be really useful!
</rant>

OK, thanks guys! Todd, thanks for the example. I'll try it out.

<edit>
Todd, that script worked like a charm! Just to note, I added a WHERE dbid > 6 to skip the 6 built-in databases since those are not being moved or otherwise touched. Thanks Again!
</edit>
------------------------
GENERAL-ly speaking...

Edited by - AjarnMark on 03/11/2002 16:42:22
Go to Top of Page

jhersey29
Starting Member

1 Post

Posted - 2012-03-13 : 17:53:29
EXEC sp_MSforeachtable @command1="DBCC DBREINDEX( '?','',80)"
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-13 : 22:07:55
This has to be a new record.

10 year necro


Uhhh, thanks for the "contribution"
Go to Top of Page
   

- Advertisement -