How to "Rebuild" all the indexes of all the tables in all the databases?

Question

Post reply

How to "Rebuild" all the indexes of all the tables in all the databases?

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
December 19, 2009 at 12:30 pm

#156517

Presently I'm using the below t-sql to manually rebuild the indexes of all the tables in the db:AdventureWorks
use adventureWorks
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
===================================================================
How can I modify the query to "build all the indexes of all the tables of the databases"?
Pls suggest.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 1

Do we have any T-sql code to know when the indexes of the tables were rebuilt last time?

Thanks.

Steve Jones - SSC Editor SSC Guru Points: 736430 More actions · Answer 2

there are lots of scripts in the Script section on this site that can help you.

you want to reuild typically not on when they were last rebuilt, but either on a schedule because of data changes, or based on fragmentation.

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 3

Sourav-657741 (12/19/2009)
Presently I'm using the below t-sql to manually rebuild the indexes of all the tables in the db:AdventureWorks
use adventureWorks
GO
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
GO
===================================================================
How can I modify the query to "build all the indexes of all the tables of the databases"?
Pls suggest.

Hi,

"sp_MSforeachtable" undocumented system stored procedures could not be supported in the future SQL Server versions.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

Sourav-657741 (12/19/2009)
Do we have any T-sql code to know when the indexes of the tables were rebuilt last time?

Hi,

Please read the 1'st link added in my signature this ll help u.

I wrote custom index rebuild script that maintaining the history table.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Please also note that DBCC DBREINDEX is deprecated, should not be used in new development and will be removed in a future version of SQL. The replacement is ALTER INDEX ... REBUILD

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 6

I'm not sure what's available in 2k8, but if you lookup DBCC SHOWCONTIG, there's a fully written script near the bottom of that help listing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)