DB reindex script - 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

SQL Server 2000 Forums

SQL Server Administration (2000)

DB reindex script

Author

Topic

Jaybee from his castle
Yak Posting Veteran

64 Posts

Posted - 2006-05-11 : 06:24:47

Hi all,

Does anyone have a good DB Reindex script I can use? Here's the one I've got from another website, but it keeps falling over, looking for tables that aren't there and then (presumably) the whole job gets rolled back:

CREATE PROC ind_rebuild
AS
DECLARE @TableName sysname
DECLARE cur_reindex CURSOR FOR
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'base table'
OPEN cur_reindex
FETCH NEXT FROM cur_reindex INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Reindexing ' + @TableName + ' table'
DBCC DBREINDEX (@TableName, ' ', 80)
FETCH NEXT FROM cur_reindex INTO @TableName
END
CLOSE cur_reindex
DEALLOCATE cur_reindex
GO

On a sidenote, doesn't CREATE PROC create another instance of 'Ind_rebuild' every time it's run??

Thanks,

Jaybee.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-11 : 06:33:41

It creates a stored procedure then you should run the proc. There's no need to recreate it in the same database.
It won't roll back any dbreindex that has completed.
Which table is it failing on? It doesn't take into account the owner so that's probably the problem.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-05-11 : 06:35:37

"Does anyone have a good DB Reindex script I can use"

http://weblogs.sqlteam.com/tarad/category/95.aspx

"doesn't CREATE PROC create another instance of 'Ind_rebuild' every time it's run??"

No, but it will fail if that Sproc already exists - such scripts are usually preceded by an "If already exists ... Drop ...." type statement

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources