Knowing the last time the UPDATE STATISTICS statement was run against all the table indexes in a database can be useful information in and of itself, so I wrote sp_DBAGetUpdateStats to report this information. After this stored procedure retrieves the date and time of the last UPDATE STATISTICS run for each table index, it sorts the retrieved datetime values in reverse chronological order. Figure A shows sample results.
The sp_DBAGetUpdateStats stored procedure is designed to reside in the master database, but you can store it in another database as long the call to the stored procedure is fully qualified. If you store sp_DBAGetUpdateStats in the master database, though, you can invoke this stored procedure from the Query Analyzer regardless of the database the current session is using.
The sp_DBAGetUpdateStats stored procedure takes only one mandatory parameter: the name of the target database. For example, if you want to list the last time the UPDATE STATISTICS statement was run against all the table indexes in the AdventureWorks database, you'd use the command
- EXEC sp_DBAGetUpdateStats
You can download sp_DBAGetUpdateStats by by clicking the Download the Code button near the top of the page. This stored procedure works on SQL Server 2008 and SQL Server 2005.