sys.dm_db_stats_properties (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

The sys.dm_db_stats_properties dynamic management function returns statistics for the specified database object in the current database.

For partitioned tables, also see the similar sys.dm_db_incremental_stats_properties.

Syntax

sys.dm_db_stats_properties (object_id, stats_id)  

Arguments

object_id

The ID of the object in the current database. object_id is int.

stats_id

The ID of statistics for the specified object_id. You can get the statistics ID from the sys.stats dynamic management view. stats_id is int.

Table returned

Remarks

The sys.dm_db_stats_properties function returns an empty rowset under any of the following conditions:

• object_id or stats_id is NULL.
• The specified object isn't found or doesn't correspond to a table or indexed view.
• The specified statistics ID doesn't correspond to existing statistics for the specified object ID.
• The current user doesn't have permissions to view the statistics object.

This behavior allows for the safe usage of sys.dm_db_stats_properties when cross applied to rows in views such as sys.objects and sys.stats.

Statistics update date is stored in the statistics blob object together with the histogram and density vector, not in the metadata. When no data is read to generate statistics data, the statistics blob isn't created, the date isn't available, and the last_updated column is NULL. This condition applies to filtered statistics for which the predicate doesn't return any rows, or to new empty tables.

Permissions

You need SELECT permissions on statistics columns, or you need to own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Examples

A. Simple example

The following example returns information about the statistics for the Person.Person table in the AdventureWorks database.

SELECT * FROM sys.dm_db_stats_properties (object_id('Person.Person'), 1);

B. Return all statistics properties for a table

The following example returns properties of all statistics that exist for the table Sales.SalesOrderDetail.

SELECT 
    OBJECT_SCHEMA_NAME(stat.object_id) AS schema_name,
    OBJECT_NAME(stat.object_id)        AS table_name,
    sp.stats_id, 
    stat.[name], 
    stat.filter_definition, 
    sp.last_updated, 
    sp.[rows], 
    sp.rows_sampled, 
    sp.steps,
    sp.unfiltered_rows, 
    sp.modification_counter   
FROM sys.stats AS stat   
OUTER APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = OBJECT_ID(N'Sales.SalesOrderDetail')

C. Return statistics properties for frequently modified objects

The following example returns all tables, indexed views, and statistics in the current database for which the leading column was modified more than 1,000 times since the last statistics update.

SELECT 
    OBJECT_SCHEMA_NAME(obj.[object_id]) AS schema_name,
    obj.[name],
    stat.[name],
    stat.stats_id, 
    sp.last_updated, 
    sp.modification_counter  
FROM sys.objects AS obj   
INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id  
OUTER APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE sp.modification_counter > 1000;

Related content

• DBCC SHOW_STATISTICS (Transact-SQL)
• sys.stats (Transact-SQL)
• Object Related Dynamic Management Views and Functions (Transact-SQL)
• Dynamic Management Views and Functions (Transact-SQL)
• sys.dm_db_incremental_stats_properties (Transact-SQL)
• sys.dm_db_stats_histogram (Transact-SQL)