Export (0) Print
Expand All
Expand Minimize

sp_helpstats (Transact-SQL)

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

Important note Important

This feature will be removed in the next version of Microsoft 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.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database

Topic link icon Transact-SQL Syntax Conventions

sp_helpstats[ @objname = ] 'object_name' 
     [ , [ @results = ] 'value' ]

[ @objname=] 'object_name'

Specifies the table on which to provide statistics information. object_name is nvarchar(520) and cannot be null. A one- or two-part name can be specified.

[ @results=] 'value'

Specifies the extent of information to provide. Valid entries are ALL and 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. value is nvarchar(5) with a default of STATS.

0 (success) or 1 (failure)

The following table describes the columns in the result set.

Column name

Description

statistics_name

The name of the statistics. Returns sysname and cannot be null.

statistics_keys

The keys on which statistics are based. Returns nvarchar(2078) and cannot be null.

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

Requires membership in the public role.

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

USE AdventureWorks2012;
GO
EXEC sp_createstats;
GO
EXEC sp_helpstats 
@objname = 'Sales.Customer',
@results = 'ALL';

Here is the result set.

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

Community Additions

ADD
Show:
© 2014 Microsoft