How to rebuild index and update statistics for all the tables in database.
Posted by Karthick P.K on September 26, 2010
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN' -- {can be run anytime}
Exec sp_MSforeachtable "dbcc dbreindex('?')" --- {Always run this on a off-peak hour on any SQL Server instance}
Thanks
Karthick
Like this:
Like Loading...
Related
This entry was posted on September 26, 2010 at 1:57 PM and is filed under Optimizer, Performance, SQL General, SQL Query.
Tagged: Rebuild indexfor all tables, reindex all tables in database, Script to rebuild index for all tables, SQL General, SQL Query. You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.
MSSQLWIKI 
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] How to rebuild index and update statistics for all the tables in database. […]
saravana said
Hi Karthick,
Thanks for the post. i am a new bee sql server admin. When i try to update statistics for the db, the server hangs. So try to update part by part and i found that it is getting stuck in few tables. So i excluded those tables and try to update, it worked. I try to manually update those tables (which hanged), it hangs. Is there any way i can update statistics in those tables?
itats said
It’s difficult to find experienced people about this subject,
but you sound like you know what you’re talking
about! Thanks