sp_helpartition

Description

Lists partition-related information of a table or index.

Syntax

sp_helpartition [ tabname [, { null | indexname | ‘all’ }[, partitionname ] ] ]

Parameters

tabname

is the name of a table in the current database.

null

specifies that information about base table partitions is to be listed.

indexname

is the name of an index in the current table. Information about this index displays.

‘all’

specifies that all index partition information is to be listed.

partitionname

is the name of the partition in the base table or index.

Examples

Example 1

Returns summary and detailed information about the data partitions in the titles table.

sp_helpartition titles
go
name      type       partition_type partitions  partition_keys
--------- ---------- -------------- ----------- --------------
titles    base table range                    5  total_sales

(1 row affected)

partition_name partition_id  pages row_count segment   create_date
-------------- ------------  ----- --------- --------- -------------------
smallsales     1440005130        1         5 titleseg1 Sep 26 2005 5:44PM
smallsales2    1456005187        1         0 titleseg2 Sep 26 2005 5:44PM
smallsales3    1472005244        1         2 titleseg3 Sep 26 2005 5:44PM
mediumsales4   1488005301        1         8 titleseg4 Sep 26 2005 5:44PM
bigsales5      1504005358        1         3 titleseg5 Sep 26 2005 5:44PM

Partition_Conditions
--------------------
VALUES <= (1000)
VALUES <= (2000)
VALUES <= (3000)
VALUES <= (10000)
VALUES <= (25000)

Avg_pages   Max_pages   Min_pages   Ratio(Max/Avg)   Ratio(Min/Avg)
----------- ---------- ---------- ------------------ --------------------
1           1           1                    1.000000                    1.000000
(return status = 0)

Example 2

Returns summary partition information about the titles table and detailed information about the smallsales data partition.

sp_helpartition titles, null, smallsales
name      type       partition_type partitions  partition_keys
--------- ---------- -------------- ----------- --------------
titles    base table range                    5 total_sales

(1 row affected)

partition_name partition_id pages  row_count segment    create_date
-------------- ------------- ------ --------- ---------  ------------------
smallsales     1440005130       1         5  titleseg1 Sep 26 2005 5:44PM

Partition_Conditions
--------------------
VALUES <= (1000)
(return status = 0)

Example 3

First, creates the nonclustered index ncidx_local on the my_titles table, then returns summary partition information about my_titles and detailed information on the partition ncip4 on ncidx_local.

create nonclustered index ncidx_local on my_titles(title_id) local index
    (ncip1, ncip2, ncip3, ncip4, ncip5)
go
sp_helpartition my_titles, ncidx_local, ncip4
go
name        type        partition_type partitions  partition_keys

----------- ----------- -------------- ----------- --------------
ncidx_local local index range                   5 total_sales

(1 row affected)

partition_name partition_id pages row_count segment create_date
-------------- ------------ ----- --------- ------- ---------------------
ncip4          1584005643       1         8 default Sep 26 2005 6:06PM

Partition_Conditions
--------------------
VALUES <= (10000)
(return status = 0)

Usage


Accuracy of results

Permissions

Any user can execute sp_helpartition.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Catalog system procedures sp_statistics

Commands alter table, create table, select into

System procedures sp_helpsegment