Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

List all the statistics with their last updated date in a database

From Wiki

Jump to: navigation, search

The following query will list all the statistics, the date the statistics were updated and the name of the Table/View/SQL table-valued-function for a database

  1. SELECT name AS StatisticsName,
  2.     STATS_DATE(OBJECT_ID, stats_id) AS StatisticsUpdateDate,
  3.     OBJECT_NAME(OBJECT_ID) as ObjectName
  4. FROM sys.stats
  5. WHERE OBJECTPROPERTYEX(OBJECT_ID,'IsMSShipped') = 0




The sys.stats Object Catalog View has a couple of more columns if you want to know more info. Here are the additional columns

Auto_created
Statistics were auto-created by the query processor.

user_created
Statistics were explicitly created by the user.

no_recompute
Statistics were created with the NORECOMPUTE option.

has_filter
1 = Statistics have a filter and are computed only on rows that satisfy the filter definition. 0 = Statistics do not have a filter and are computed on all rows.

filter_definition
Expression for the subset of rows included in filtered statistics. NULL = Non-filtered statistics.


Here is the query with the additional columns:

  1. SELECT
  2.     name AS StatisticsName,
  3.     STATS_DATE(OBJECT_ID, stats_id) AS StatisticsUpdateDate,
  4.     OBJECT_NAME(OBJECT_ID) as ObjectName,
  5.     auto_created,
  6.     user_created,
  7.     has_filter,
  8.     filter_definition,
  9.     no_recompute
  10. FROM sys.stats
  11. WHERE OBJECTPROPERTYEX(OBJECT_ID,'IsMSShipped') = 0


Part of SQL Server Admin Hacks

Contributed by: --SQLDenis 20:39, 20 March 2010 (GMT)

675 Rating: 2.7/5 (49 votes cast)