Lists partition-related information of a table or index.
sp_helpartition [ tabname [, { null | indexname | ‘all’ }[, partitionname ] ] ]
is the name of a table in the current database.
specifies that information about base table partitions is to be listed.
is the name of an index in the current table. Information about this index displays.
specifies that all index partition information is to be listed.
is the name of the partition in the base table or index.
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)
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)
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)
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 a table name is not supplied, sp_helpartition lists the owner, table name, number of partitions, and the partition type of all user tables in the current database.
If ‘all’ is specified 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.
If a particular index is specified, 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.
If the partition name is specified, sp_helpartition displays only the partition-level information for that partition.
If only the table name is specified, sp_helpartition displays table-level index partition information for the base table and partition-level information for all partitions in the base table.
If null is specified 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.
The values reported in the “pages” column may differ from the actual values. To determine whether the count is inaccurate, run sp_statistics and sp_helpartition to compare the data page count. The count provided by sp_statistics is always accurate.
If the page count reported by sp_statistics differs from the sum of the partition pages reported by sp_helpartition by more then 5 percent, run one of these commands to update the partition statistics:
dbcc checkdb
dbcc checktable
update all statistics
update table statistics
Then, rerun sp_helpartition for an accurate report.
Any user can execute sp_helpartition.
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 |
|
Catalog system procedures sp_statistics
Commands alter table, create table, select into
System procedures sp_helpsegment
