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
 General SQL Server Forums
 Script Library
 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 :  16:50:16  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Credit for this script really goes to ToddV (see http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13737)

The following script issues a DBREINDEX command against every table in every database on your server. It can be modified to issue other commands, but this one in particular is helpful as we are migrating about 30 databases from SQL 7 to SQL 2000 (new server) and re-indexing is recommended. Here's the script:


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

SELECT @SQL = @SQL + 'EXEC ' + NAME + '..sp_MSforeachtable @command1=''DBCC DBREINDEX (''''*'''')'', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid > 6 -- skip the 6 built-in databases. Remove to process ALL

PRINT @SQL -- Only if you want to see the code you're about to execute.
EXEC (@SQL)


Notes: There is a limit (nvarchar 4000) to how big your command can be, so too many databases will halt this.

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

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 03/11/2002 :  17:01:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
Mark-

Couldn't you use varchar(8000) instead? EXEC can use varchar, and sp_MSforeachtable also takes varchar values.

Go to Top of Page

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 03/11/2002 :  17:26:56  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Yes, you are correct. I thought of that as I was typing my note, but didn't take time then to test it. I just tested it and it worked fine. Of course, this means there's still a limit, but it's much higher... somewhere around 80 DBs, I'm guessing, depending on the length of your database names.

Also, you can change the * to a ?, which is the default replacechar, and then strip off the , @replacechar=''*''' syntax. That was a leftover from something else I tried and is not necessary here.

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

Edited by - AjarnMark on 03/11/2002 17:27: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.08 seconds. Powered By: Snitz Forums 2000