This topic has not yet been rated - Rate this topic

sp_helpstats (Transact-SQL)

SQL Server 2008 R2

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

Important
This feature will be removed in a future 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.

Topic link icon Transact-SQL Syntax Conventions

Syntax


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

Arguments

[ @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.

Return Code Values

0 (success) or 1 (failure)

Result Sets

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.

Remarks

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).

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 AdventureWorks2008R2 database by executing sp_createstats. Then, sp_helpstats is run to find the statistics created on the Customer table.

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

Here is the result set.

statistics_name                  statistics_keys
----------------------------------------------------------------
_WA_Sys_00000002_2A4B4B5E        PersonID
_WA_Sys_00000003_2A4B4B5E        StoreID
_WA_Sys_00000005_2A4B4B5E        AccountNumber
AK_Customer_AccountNumber        AccountNumber
AK_Customer_rowguid              rowguid
CustomerType                     CustomerType
IX_Customer_TerritoryID          TerritoryID
ModifiedDate                     ModifiedDate
PK_Customer_CustomerID           CustomerID

Reference

System Stored Procedures (Transact-SQL)

Database Engine Stored Procedures (Transact-SQL)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD