SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Reindex All Tables in All Databases
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 03/11/2002 :  14:14:13  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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

USA
218 Posts

Posted - 03/11/2002 :  14:27:55  Show Profile  Reply with Quote
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

USA
15676 Posts

Posted - 03/11/2002 :  14:29:05  Show Profile  Visit robvolk's Homepage  Reply with Quote
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

USA
3246 Posts

Posted - 03/11/2002 :  16:21:59  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
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 Posts

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

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 03/13/2012 :  22:07:55  Show Profile  Visit russell's Homepage  Reply with Quote
This has to be a new record.

10 year necro


Uhhh, thanks for the "contribution"

Edited by - russell on 03/13/2012 22:08:43
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.33 seconds. Powered By: Snitz Forums 2000