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

data_pgs, a system function, returns the number of pages used by a table (doampg) or index (ioampg). You must use this function in a query run against the sysindexes table. For more information on system functions, see “System functions”.
data_pgs works only on objects in the current database.
The result does not include pages used for internal structures. To see a report of the number of pages for the table, clustered index, and internal structures, use used_pgs.

Accuracy of results

If used on the transaction log (syslogs), the result may not be accurate and can be off by up to 16 pages.

Errors

Instead of returning an error, data_pgs returns 0 if any of the following are true:
- The object_id does not exist in sysobjects
- The control_page_id does not belong to the table specified by object_id
- The object_id is -1
- The page_id is -1

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

Any user can execute data_pgs.