used_pgs

Description

Returns the number of pages used by a table or index. For an all-pages-locked table with a clustered index, it returns the sum of the table and index pages.

Syntax

used_pgs(object_id, doampg, ioampg)

Parameters

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.

doampg

is the page number for the object allocation map of a table or clustered index, stored in the doampg column of sysindexes.

ioampg

is the page number for the allocation map of a nonclustered index, stored in the ioampg column of sysindexes.

Examples

Example 1

Returns the number of pages used by the data and clustered index of the titles table:

select name, id, indid, doampg, ioampg
from sysindexes where id = object_id("titles")
name          id          indid  doampg   ioampg  
------------- ----------- ------ -------- ------- 
titleidind    208003772     1      560     552 
titleind      208003772     2        0     456 

select used_pgs(208003772, 560, 552)
-----------
          6

Example 2

Returns the number of pages used by the stores table, which has no index:

select name, id, indid, doampg, ioampg
from sysindexes where id = object_id("stores")
name          id          indid  doampg   ioampg
------------- ----------- ------ -------- -------
stores          240003886      0     464       0 
select used_pgs(240003886, 464, 0)
-----------
          2

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute used_pgs.

See also

Functions data_pgs, object_id