Execute UPDATE STATISTICS for all SQL Server Databases

By:   |   Updated: 2008-10-17   |   Comments (9)   |   Related: More > Maintenance

Keeping Your SQL Servers Running Strong

Free MSSQLTips Webinar: Keeping Your SQL Servers Running Strong

Your database servers need care and feeding in order to keep your workloads running smoothly. Indexes and statistics need to be kept up to data and defragmented, databases should be checked for consistency, and of course databases should be backed up. In this session you will learn what you need to do to maintain your servers, how you should perform that maintenance, and when you should do those tasks and how frequently.

If you're like me, you have a SQL Agent job in place to rebuild or reorganize only the indexes in your databases that truly require such actions.  If you rely on the standard maintenance plans in Microsoft SQL Server, a Scorched-Earth policy of rebuilding all indexes occurs.  That is whether such actions are required or not for a specific index, a rebuild of the index and all the locking and churning in the logs occurs.  That is why so many of us "roll our own" index maintenance solutions as it were.  It is also one of my biggest peeves with Microsoft - why was this not built into SQL Server 2008?  Ah, at any rate, by only maintaining indexes that are fragmented, statistics updates do not occur globally against the tables/indexes in your databases.  What we need is a quick solution for updating all the statistics for every database on our SQL Server instance. 

Before you go ahead and state the fact that you have AUTO_UPDATE_STATISTICS ON for your databases remember that does not mean that they are being updated! 

Bah! You say?  Well think about this for a second.  I've touched upon this in an earlier tip, SQL Server's engine will update the statistic when:

  • When data is initially added to an empty table
  • The table had > 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records since that collection date
  • The table had < 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records + 20% of the row count from the previous statistics collection date

Based upon this criteria, there will be many cases where the underlying data changes in such a way or in such levels that the statistics that exist for an index will not be indicative of the actual data in the database.  Because of this you simply can not rely on the engine to keep you statistics in check and current.  Here is a simple block of code that will iterate through all your databases in order to build the sp_updatestats command that can then be copied and pasted into a new query window for execution.  This code will work with all current and previous versions of SQL Server back through SQL 7.0.

DECLARE @DB sysname 

FROM master..sysdatabases
WHERE [name] NOT IN ('model''tempdb')
ORDER BY [name]
@SQL 'USE [' @DB +']' CHAR(13) + 'EXEC sp_updatestats' CHAR(13

On my test server this code yields the following results.


The next step is to copy this text, paste it into a query window in SQL Server Management Studio, then execute it against the instance.  Alternately you may choose only to execute it against select databases, but that is entirely up to you.

USE [Dummy]
EXEC sp_UpdateStats

USE [master]
EXEC sp_UpdateStats

USE [msdb]
EXEC sp_UpdateStats

EXEC sp_UpdateStats

EXEC sp_UpdateStats

USE [Northwind]
EXEC sp_UpdateStats

USE [Sitka]
EXEC sp_UpdateStats

USE [Utility]


I've provided a sample of the output generated by the collection of SQL statements executed above. As you can see, the engine still will review the statistics to see if they warrant updating. It will ignore those that are acceptable and will update only the statistics that require such action. I can assure you that each of the databases in the listing above had AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS both set to ON, yet the following results are indicative of statistics that can become outdated.


Next Steps

Last Updated: 2008-10-17

get scripts

next tip button

About the author
MSSQLTips author Tim Ford Tim Ford is a Senior Database Administrator with MindBody.

View all my tips
Related Resources

Comments For This Article

Wednesday, December 18, 2019 - 4:10:57 AM - name Back To Top (83465)


Wednesday, March 09, 2016 - 4:01:55 AM - pardha Back To Top (40895)

 Hi I want the output like below for all databases with including table names. could you please provide the script how to do that.



USE [AdventureWorks2012]



UPDATE STATISTICS AdventureWorks2012.Production.ScrapReason WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012.HumanResources.Shift WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012..Production.ProductCategory WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Purchasing.ShipMethod WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Production.ProductCostHistory WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Production.ProductDescription WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Sales.ShoppingCartItem WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012Production.ProductDocument WITH FULLSCAN

UPDATE STATISTICS AdventureWorks2012dbo.DatabaseLog WITH FULLSCAN

Thursday, August 21, 2014 - 4:52:40 AM - Behamooz.com Back To Top (34217)

it is better

SELECT 'USE [' + name +']' + CHAR(13) + 'EXEC sp_updatestats' + CHAR(13)   FROM sys.databases AS d

Wednesday, July 16, 2014 - 11:45:57 AM - Joerg Hencke Back To Top (32736)


thanks for that clear article. I just saw 2 typos ('

Correct should be:


The table had < 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records since that collection date

The table had > 500 records when statistics were last collected and the lead column of the statistics object has now increased by 500 records + 20% of the row count from the previous statistics collection date



Thursday, January 31, 2013 - 1:42:35 PM - EuniceRH Back To Top (21851)

does this apply to SQL Server 2008 R2 Express as well?

Tuesday, October 09, 2012 - 9:33:34 AM - Greg Robidoux Back To Top (19830)

@Dinesh - if you delete a most of the data then you should also rebuild your indexes which will take care of updating statistics.

Look at tips regarding ALTER INDEX...REBUILD


Monday, October 08, 2012 - 11:45:01 PM - Dinesh Back To Top (19827)

There is one table who having 554535 rows data.I had delete 3lAKH DATA then it required ??

EXEC sp_updatestats database ??

Sunday, October 19, 2008 - 5:15:16 AM - timmer26 Back To Top (2005)

You are correct, this is a much more streamlined approach, I wanted to stick to documented processes for this tip however.  I provided a tip earlier this year on sp_MSforeachdb matter of fact.  Both work well though Jerry.  You may want to check the results though.  You may find that, though you thought you excluded the system databases, that they were indeed NOT excluded.

Friday, October 17, 2008 - 7:43:25 AM - jerryhung Back To Top (1997)

 This is what I use, simpler too


sp_MSforeachdb 'if ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') use ? exec sp_updatestats'


Recommended Reading

Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table

Move SQL Server Maintenance Plan from One Server to Another

Update Statistics for All Tables and Databases in a SQL Server Instance

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks

SQL Server Maintenance Plans Maintenance Cleanup Task

get free sql tips
agree to terms