TechNet
Export (0) Print
Expand All
Collapse the table of content
Expand the table of content
Expand Minimize

sys.stats (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Contains a row for each statistics object that exists for the tables, indexes, and indexed views in the database in SQL Server. Every index will have a corresponding statistics row with the same name and ID (index_id = stats_id), but not every statistics row has a corresponding index.

The catalog view sys.stats_columns provides statistics information for each column in the database. For more information about statistics, see Statistics.

Column nameData typeDescription
object_idintID of the object to which these statistics belong.
namesysnameName of the statistics. Is unique within the object.
stats_idintID of the statistics. Is unique within the object.
auto_createdbitIndicates whether the statistics were automatically created by SQL Server.

0 = Statistics were not automatically created by SQL Server.

1 = Statistics were automatically created by SQL Server.
user_createdbitIndicates whether the statistics were created by a user.

0 = Statistics were not created by a user.

1 = Statistics were created by a user.
no_recomputebitIndicates whether the statistics were created with the NORECOMPUTE option.

0 = Statistics were not created with the NORECOMPUTE option.

1 = Statistics were created with the NORECOMPUTE option.
has_filterbit0 = Statistics do not have a filter and are computed on all rows.

1 = Statistics have a filter and are computed only on rows that satisfy the filter definition.
filter_definitionnvarchar(max)Expression for the subset of rows included in filtered statistics.

NULL = Non-filtered statistics.
is_temporarybitApplies to: SQL Server 2012 through SQL Server 2016.

Indicate whether the statistics is temporary. Temporary statistics support Always On Availability Groups secondary databases that are enabled for read-only access.

0 = The statistics is not temporary.

1 = The statistics is temporary.
is_incrementalbitApplies to: SQL Server 2014 through SQL Server 2016.

Indicate whether the statistics are created as incremental statistics.

0 = The statistics are not incremental.

1 = The statistics are incremental.

The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

The following examples returns all the statistics and statistics columns for the HumanResources.Employee table.

USE AdventureWorks2012;  
GO  
SELECT s.name AS statistics_name  
      ,c.name AS column_name  
      ,sc.stats_column_id  
FROM sys.stats AS s  
INNER JOIN sys.stats_columns AS sc   
    ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id  
INNER JOIN sys.columns AS c   
    ON sc.object_id = c.object_id AND c.column_id = sc.column_id  
WHERE s.object_id = OBJECT_ID('HumanResources.Employee');  
  

Object Catalog Views (Transact-SQL)
Catalog Views (Transact-SQL)
Querying the SQL Server System Catalog FAQ

Community Additions

ADD
Show:
© 2016 Microsoft