data_pgs

Description

Returns the number of pages used by the specified table or index.

Syntax

data_pgs([dbid], object_id, {data_oam_pg_id | index_oam_pg_id}

Parameters

dbid

is the dbid of the database that contains the data pages.

object_id

is an object ID for a table, view, or other database object. These are stored in the id column of sysobjects.

data_oam_pg_id

is the page ID for a data OAM page, stored in the doampg column of sysindexes.

index_oam_pg_id

is the page ID for an index OAM page, stored in the ioampg column of sysindexes.

Examples

Example 1

Estimates the number of data pages used by user tables (which have object IDs that are greater than 100). An indid of 0 indicates a table without a clustered index; an indid of 1 indicates a table with a clustered index. This example does not include nonclustered indexes or text chains:

select sysobjects.name,
Pages = data_pgs(sysindexes.id, doampg)
from sysindexes, sysobjects
where sysindexes.id = sysobjects.id
    and sysindexes.id > 100
    and (indid = 1 or indid = 0)

Example 2

Estimates the number of data pages used by user tables (which have object IDs that are greater than 100), nonclustered indexes, and page chains:

select sysobjects.name,
Pages = data_pgs(sysindexes.id, ioampg)
from sysindexes, sysobjects
where sysindexes.id = sysobjects.id
    and sysindexes.id > 100
    and (indid > 1)

Usage


Accuracy of results


Errors

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

Any user can execute data_pgs.

See also

Functions object_id, rowcnt

System procedure sp_spaceused