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
name type partition_type partitions partition_keys ------ ------- -------------- ----------- -------------- titles base table range 4 pubdate partition_name partition_id pages segment create_date -------------- ------------ -------- -------- ---------------- q1 1136004047 1 default Oct 13 2005 10:33AM q2 1152004104 1 default Oct 13 2005 10:33AM q3 1168004161 1 default Oct 13 2005 10:33AM q4 1184004218 1 default Oct 13 2005 10:33AM Partition_Conditions -------------------- VALUES <= (“3/31/2006”) VALUES <= (“6/30/2006”) VALUES <= (“9/30/2006”) VALUES <= (“12/31/2006”) Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg) --------- --------- --------- --------------- ------------ 1 1 1 1.000000 1.000000
Displays summary partition information about titles table and detailed information about the q1 data partition.
sp_helpartition titles, null, q1
name type partition_type partitions partition_keys ------ ------- -------------- ----------- -------------- titles base table range 4 pubdate partition_name partition_id pages segment create_date -------------- ------------ -------- -------- ---------------- q1 1136004047 1 default Oct 13 2005 10:33AM Partition_Conditions -------------------- VALUES <= (“3/31/2006”)
Displays summary and detailed information about the index title_idx on the titles table.
sp_helpartition titles, title_idx
name type partition_type partitions partition_keys ------ ------- -------------- ---------- -------------- title_idx local index range 4 pubdate partition_name partition_id pages segment create_date -------------- ------------ ------ -------- ---------------- title_idx_1029575675 1029575675 1 default Oct 13 2005 10:33AM title_idx_1045575732 1045575732 1 default Oct 13 2005 10:33AM title_idx_1061575789 1061575789 1 default Oct 13 2005 10:33AM title_idx_1077575846 1077575846 1 default Oct 13 2005 10:33AM Partition_Conditions -------------------- VALUES <= (“3/31/2006”) VALUES <= (“6/30/2006”) VALUES <= (“9/30/2006”) VALUES <= (“12/31/2006”)
Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg) --------- --------- --------- --------------- ------------ 1 1 1 1.000000 1.000000
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
| Copyright © 2005. Sybase Inc. All rights reserved. |
|
|
