Returns the number of pages used by the specified table, index, or a specific partition. The result does not include pages used for internal structures.
This function replaces data_pgs and ptn_data_pgs from versions of Adaptive Server earlier than 15.0.
data_pages(dbid, object_id [, indid [, ptnid]])
is the database ID of the database that contains the data pages.
is an object ID for a table, view, or other database object. These are stored in the id column of sysobjects.
is the index ID of the target index.
is the partition ID of the target partition.
Returns the number of pages used by the object with a object ID of 31000114 in the specified database (including any indexes):
select data_pages(5, 31000114)
(In cluster environments) Returns the number of pages used by the object in the data layer, regardless of whether or not a clustered index exists:
select data_pages(5, 31000114, 0)
(In cluster environments) Returns the number of pages used by the object in the index layer for a clustered index. This does not include the pages used by the data layer:
select data_pages(5, 31000114, 1)
Returns the number of pages used by the object in the data layer of the specific partition, which in this case is 2323242432:
select data_pages(5, 31000114, 0, 2323242432)
In the case of an APL (all-pages lock) table, if a clustered index exists on the table, then passing in an indid of:
0 – reports the data pages.
1 – reports the index pages.
All erroneous conditions return a value of zero, such as when the object_id does not exist in the current database, or the targeted indid or ptnid cannot be found.
Instead of consuming resources, data_pages discards the descriptor for an object that is not already in the cache.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute data_pages.
Functions object_id, row_count
System procedure sp_spaceused
