Building Indexes for all databases

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

SQL Server Administration (2000)

Building Indexes for all databases

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2006-02-22 : 07:11:09

Hi guys

i want to use the following script for building indexes on all the databases on the testing sql server.

But it throws up an error as

INCORRECT SYNTAX NEAR DATABASENAME

The script is as follows

use master

declare @databasename varchar(255)
declare @tablename varchar(255)

declare dbcursor cursor for
select [name] from sysdatabases

open dbcursor

fetch next from dbcursor into @databasename
while @@fetch_status = 0

begin
USE @databasename

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @TableName
END

CLOSE TableCursor

DEALLOCATE TableCursor

end

close DatabaseCursor

Deallocate DatabaseCursor

What could be the problem??

Thanks
Vic

mallier
Starting Member

24 Posts

Posted - 2006-02-22 : 07:28:01

'use @database' statement is wrong. u cannot use that way in sql server.
i have a sp which do reindex on all db.see code below.

use master
go

CREATE PROC Alldb_reindexAllTables_sp
as

declare @sql nvarchar(4000)

--- reindexing all tables except 'tempdb','msdb','pubs','master','Northwind' ---------

set @sql = ''
select @sql = @sql + 'exec ' + name + '..sp_MSforeachtable @command1=''dbcc dbreindex (''''*'''') WITH NO_INFOMSGS '', @replacechar=''*''' + char(13)
from master..sysdatabases where name not in ('tempdb','msdb','pubs','master','Northwind','model')
--print @sql
exec (@sql)

--exec master..Alldb_reindexAllTables_sp

cheers,
http://mallier.blogspot.com

svicky9
Posting Yak Master

232 Posts

Posted - 2006-02-22 : 07:56:39

thanks

Vic

Kristen
Test

22859 Posts

Posted - 2006-02-22 : 08:17:32

Note that dbreindex is quite demanding of resources on the server, and you may want to only rebuild indexes that are fragmented (or to use DEFRAG instead of REINDEX).

We use DEFRAG on tables with more than just a few blocks, and REINDEX on the "little" ones, but only if they are more than 20% fragmented.

I'm pretty sure that Tara has a routine to tackle this in her blog http://weblogs.sqlteam.com/tarad/category/95.aspx otherwise see http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=444&whichpage=2

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources