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.
used_pgs(object_id, doampg, ioampg)
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.
is the page number for the object allocation map of a table or clustered index, stored in the doampg column of sysindexes.
is the page number for the allocation map of a nonclustered index, stored in the ioampg column of sysindexes.
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
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
used_pgs, a system function, returns:
For all-pages-locked tables with a clustered index – the sum of the table and index pages
For data-only-locked tables and tables with no clustered index – the number of used pages in the table
For clustered and nonclustered indexes on data-only-locked tables – the number of pages in the index
In the examples, indid 0 indicates a table; indid 1 indicates a clustered index; an indid of 2–250 is a nonclustered index; and an indid of 255 is text or image data.
used_pgs only works on objects in the current database.
Each table and each index on a table has an object allocation map (OAM), which contains information about the number of pages allocated to and used by an object. This information is updated by most Adaptive Server processes when pages are allocated or deallocated. The sp_spaceused system procedure reads these values to provide quick space estimates. Some dbcc commands update these values while they perform consistency checks.
For general information about system functions, see “System functions”.
SQL92 – Compliance level: Transact-SQL extension.
Any user can execute used_pgs.
