Chapter 2: Transact-SQL Functions

used_pages

Description

Reports the number of pages used by a table, an index, or a specific partition. Unlike data_pages, used_pages does include pages used for internal structures. This function replaces the used_pgs function used in versions of Adaptive Server earlier than 15.0.

Syntax

used_pages(dbid, object_id [, indid [, ptnid]])

Parameters

dbid: the database id where target object resides.

object_id: is the object ID of the table for which you want to see the used pages. To see the pages used by an index, specify the object ID of the table to which the index belongs.

indid: is the index id of interest.

ptnid: is the partition id of interest.

Examples

Example 1

Returns the number of pages used by the object with a object ID of 31000114 in the specified database (including any indexes):

select used_pages(5, 31000114)

Example 2

Returns the number of pages used by the object in the data layer, regardless of whether or not a clustered index exists:

select used_pages(5, 31000114, 0)

Example 3

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 used_pages(5, 31000114, 1)

Example 4

Returns the number of pages used by the object in the data layer of the specific partition, which in this case is 2323242432:

select used_pages(5, 31000114, 0, 2323242432)

Usage

used_pages(dbid, objid, 0) is identical to used_pages(dbid, objid, 1) in the case of an all-page lock table with a clustered index. This is similar to the old used_pgs(objid, doampg, ioampg) function.
All erroneous conditions result in a return value of zero.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute used_pgs.