DB Reindex script needed

Question

Post reply

DB Reindex script needed

Jay Bee-249994

SSCrazy

Points: 2393
More actions
May 11, 2006 at 4:24 am

#152964

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.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 1

This should do it

sp_MSforeachtable

@command1="print '?' dbcc DBREINDEX ('?')"

so_MSForEachTable is undocumnented stored procedure

hth

David

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 2

I don't understand the syntax too well, where do I change the database name or how do I pass the name into the Exec statement? And can I change the fill-factors, or are they just rebuilt as they originally were?

Thanks,

Jaybee.

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 3

It just enumrates each table in the database so to use it in a particular database you would just do this

USE Database

GO

sp_MSforeachtable @command1 = "print '?' dbcc DBREINDEX ('?')"

This will only rebuild it with the original fill factors.

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 4

USE Northwind (for example)

GO

sp_MSforeachtable @command1 = "print '?' dbcc DBREINDEX ('?')"

That's it?!?!??? I can put that in a scheduled job and have it reindex my db???

Jaybee.

David Scotland-132255 SSCertifiable Points: 5599 More actions · Answer 5

David Scotland-132255

SSCertifiable

Points: 5599

May 11, 2006 at 3:24 pm

#637406

Yeh thats it.

tosscrosby Hall of Fame Points: 3140 More actions · Answer 6

If you're doing all the tables in the database, your can use a maintenance plan to perform it as well. Create the plan with the wizard, schedule it as frquently as you need it done and, voila, it's done!

Terry

Jay Bee-249994 SSCrazy Points: 2393 More actions · Answer 7

You know, that's just how I ended up doing it...all the other scripts kept falling over due to missing tables, but I didn't even realise the Maint Plan had a defragger...!!! Just goes to show how rusty I am.

Thanks all, but special thanks to Mr Crosby!!

Jaybee.

Kimberly Dengler Old Hand Points: 311 More actions · Answer 8

Any thoughts/ideas as to a good way to defrag/reindex a db that is in 6.5 compatibility and creates TONS of blocking locks? Using the maintenance wizard kills the db...we're online 365x24x7, so there really isn't a good time to do maintenance.