Edit

Share via

sp_helpstats (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns statistics information about columns and indexes on the specified table.

Important

This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. To obtain information about statistics, query the sys.stats and sys.stats_columns catalog views.

Transact-SQL syntax conventions

Syntax

syntaxsql 
sp_helpstats
    [ @objname = ] N'objname'
    [ , [ @results = ] N'results' ]
[ ; ]

Arguments

[ @objname = ] N'objname'

Specifies the table on which to provide statistics information. @objname is nvarchar(776), with no default. A one-part or two-part name can be specified.

[ @results = ] N'results'

Specifies the extent of information to provide. @results is nvarchar(5), with a default of STATS.

  • ALL lists statistics for all indexes and also columns that have statistics created on them.
  • STATS only lists statistics not associated with an index.

Return code values

0 (success) or 1 (failure).

Result set

The following table describes the columns in the result set.

Column name Description
statistics_name The name of the statistics. Returns sysname and can't be NULL.
statistics_keys The keys on which statistics are based. Returns nvarchar(2078) and can't be NULL.

Remarks

Use DBCC SHOW_STATISTICS to display detailed statistics information about any particular index or statistics. For more information, see DBCC SHOW_STATISTICS and sp_helpindex.

Permissions

Requires membership in the public role.

Examples

The following example creates single-column statistics for all eligible columns for all user tables in the AdventureWorks2022 database by executing sp_createstats. Then, sp_helpstats is run to find the resultant statistics created on the Customer table.

SQL 
USE AdventureWorks2022;
GO

EXEC sp_createstats;
GO

EXEC sp_helpstats
    @objname = 'Sales.Customer',
    @results = 'ALL';

Here's the result set.

Output 
statistics_name               statistics_keys
---------------------------- ----------------
_WA_Sys_00000003_22AA2996     AccountNumber
AK_Customer_AccountNumber     AccountNumber
AK_Customer_rowguid           rowguid
CustomerType                  CustomerType
IX_Customer_TerritoryID       TerritoryID
ModifiedDate                  ModifiedDate
PK_Customer_CustomerID        CustomerID

Related content