|
used_pgs()
The
system function
used_pgs()
returns the number of used
pages
of a
table
or
index.
For a table with
all-pages lock
scheme and a
clustered index,
the return value consists of the sum of the pages used by the index and the table.
For a table with
data-only-locked
scheme, and for table without a
clustered Index, the function "used_pgs" returns the number of used pages
in the table.
For tables with data-only-locked scheme, with a
clustered
or
non-clustered
index
the function "used_pgs" returns the number of used pages for the
index.
The function used_pgs is replaced by the system function
used_pages
starting with ASE version 15.0.
The system function "used_pgs()" is a
T-SQL
extension. It can be executed by any
user,
but only on objects in the database currently in use.
Syntax of the System Function used_pgs()
used_pages(object_id, doampg, ioampg)
The parameters of the System Function used_pgs()
"object_id"
The parameter "object_id" is the id (identification number)
of the
table
for which the number of used pages must be determined.
In order to determine the number of pages used by an
index,
one still needs to specify the "object_id" of the table to which the index belongs.
The "object_id" can be retrieved from the
column
"id" of the
system table
sysobjects.
Every table and every index has an
object allocation map
or
OAM.
These pages contain information on the number of pages assigned to an object.
This information is updated by several ASE
processes,
if pages are added or deleted.
Processes, reading this information are, among others,
sp_spaceused,
but also some
dbcc
commands.
"doampg"
The parameter "doampg" is the page number of the
object allocation map
or
OAM
of a table or clustered index.
The value "doampg" can be located in the
column doampg of the
system table
sysindexes.
"ioampg"
The parameter "ioampg" describes the page number for the
object allocation map
or
OAM
of a non-clustered index, which must be queried. The value for "ioampg" can be retrieved
from the column "ioampg" of the
system table
sysindexes.
Example for the System Function used_pgs()
1> select name, id, indid, doampg, ioampg
2> from sysindexes where id = object_id("spt_values")
3> go
name id indid doampg ioampg
------------------------------ ----------- ------ ----------- -----------
spt_valuesclust 505049804 1 1040 1032
1> select used_pgs(505049804,1040,1032)
2> go
-----------
38
(1 row affected)
Returns the sum of used pages of the table with id 505049804 ("spt_values")
and its clustered index.
See also:
ASE T-SQL - Aggregate Functions,
audit_event_name(),
col_length(),
col_name(),
curunreservedpgs(),
datachange(),
datalength(),
data_pages(),
data_pgs(),
db_id(),
db_name(),
host_id(),
host_name(),
identity(),
index_col(),
index_colorder(),
isnull(),
lct_admin(),
license_enabled(),
lockscheme(),
next_identity(),
object_id(),
object_name(),
pagesize(),
pointer_size(),
ptn_data_pgs(),
reserved_pages(),
reserved_pgs(),
rowcnt(),
row_count(),
showplan_in_xml(),
syb_sendmsg(),
syb_sendmsg(),
System Functions,
tempdb_id(),
tran_dumpable_status(),
tsequal(),
used_pages().
|
