sp_helpartition

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

• sp_helpartition lists partition related information at the table, index, and partition level. The table- or index-level partition information includes index type (whether it is a local or global index), partition type, number of partitions, and partition keys, if applicable. For each partition, the information include partition name, ID, number of pages, segment name, create date, and the partition condition if applicable.

  The summary information displays the number of pages per partition, the minimum and maximum number of pages, and the ratio between the average number of pages and the maximum or minimum number.

• If you do not supply a table name, sp_helpartition lists the owner, table name, number of partitions, and the partition type of all user tables in the current database.

• If you specify:

  • ‘all’ instead of an index name or null – sp_helpartition lists the table- and index-level partition information for each index of the specified table and of the base table.

  • A particular index – sp_helpartition lists the index-level information for that index. If the partition name is:

    • Not specified – sp_helpartition displays the partition-level information for all partitions in the index, and summary information for the partitions.

    • Specified – sp_helpartition displays only the partition-level information for that partition.

  • Only the table name – sp_helpartition displays table-level index partition information for the base table and partition-level information for all partitions in the base table.

  • Null instead of an index name, and a partition name is specified – sp_helpartition displays table-level partition information for the base table and partition-level information for the named partition—with no summary information.

• Partitions are created using create table, alter table, and select into. See these commands for more information about partitioning.

• Use sp_helpsegment to display the number of used and free pages on the segment on which the partition is stored.

See also alter table, create table, select into in Reference Manual: Commands.

Permissions

Any user can execute sp_helpartition. Permission checks do not differ based on the granular permissions settings.

Auditing

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