All Tables in All Databases - SQL Server Forums

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

General SQL Server Forums

Script Library

All Tables in All Databases

Author

Topic

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-11 : 16:50:16

Credit for this script really goes to ToddV (see [url]http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=13737[/url])

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

15732 Posts

Posted - 2002-03-11 : 17:01:01

Mark-

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

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-03-11 : 17:26:56

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources