Skip to content

sp-helpstats-transact-sql.md

Latest commit

 

History

History
109 lines (81 loc) · 4.41 KB

sp-helpstats-transact-sql.md

File metadata and controls

109 lines (81 loc) · 4.41 KB

title: "sp_helpstats (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: "sql" ms.prod_service: "database-engine, sql-database" ms.service: "" ms.component: "system-stored-procedures" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords:
  • "sp_helpstats"
  • "sp_helpstats_TSQL" dev_langs:
  • "TSQL" helpviewer_keywords:
  • "sp_helpstats" ms.assetid: 00ab3cfd-2736-4fc0-b1b2-16dd49fb2fe5 caps.latest.revision: 37 author: "edmacauley" ms.author: "edmaca" manager: "craigg" ms.workload: "Inactive" monikerRange: "= azuresqldb-current || >= sql-server-2016 || = sqlallproducts-allversions"

sp_helpstats (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx-md]

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

Important

[!INCLUDEssNoteDepNextAvoid] 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 [!INCLUDEssSampleDBobject] 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';

[!INCLUDEssResult]

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

See Also

System Stored Procedures (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)