Chapter 15: Using the Built-In Functions in Queries

System functions that return database information

The system functions return special information from the database. Many of them provide a shorthand way to query the system tables.

The general syntax of the system functions is:

select function_name(argument[s])

You can use the system functions in the select list, in the where clause, and anywhere an expression is allowed.

For example, to find the user identification number of your coworker who logs in as “harold,” type:

select user_id("harold")

Assuming that “harold” has user ID 13, the result is:

------------- 
           13 
 
(1 row affected)

Generally, the name of the function tells you what kind of information is returned.

The system function user_name takes an ID number as its argument and returns the user’s name:

select user_name(13)

--------- 
harold 
 
(1 row affected)

To find the name of the current user, that is, your name, omit the argument:

select user_name()

--------- 
dbo 
 
(1 row affected)

Adaptive Server handles user IDs as follows:

The System Administrator becomes the Database Owner in any database he or she is using by assuming the server user ID 1.
A “guest” user is always given the server user ID 1.
Inside a database, the user_name of the Database Owner is always “dbo”; his or her user ID is 1.
Inside a database, the “guest” user ID is always 2.

dbcc commands are documented in the System Administration Guide and the Reference Manual.

Table 15-1 lists the name of each system function, the argument it takes, and the result it returns. These functions are fully documented in the Reference Manual.

**Table 15-1: System functions, arguments, and results**
Function	Argument	Result
audit_event_name	(event_id)	Returns the number of an audit event.
col_length	(object_name, column_name)	Returns the defined length of column. Use datalength to see the actual data size.
col_name	(object_id, column_id [, database_id ])	Returns the column name for the specified column and table ID.
curunreservedpgs	(dbid, lstart, unreservedpgs)	Returns the number of free pages in a disk piece. If the database is open, the value is taken from memory; if the database is not in use, the value is taken from the unreservedpgs column in sysusages.
data_change	(expression)	Returns the actual length, in bytes, of the specified column or string
datalength	(expression)	Returns the length of expression in bytes. expression is usually a column name. If expression is a character constant, it must be enclosed in quotes.
data_pgs (data_pages?)	([dbid,] object_id , {doampg \| ioampg })	Returns the number of pages used by the table (doampg) or index (ioampg). The result does not include pages used for internal structures. Use the function in a query run against the sysindexes table.
db_id	([ database_name])	Returns the database ID number. database_name must be a character expression; if it is a constant expression, it must be enclosed in quotes. If no database_name is supplied, db_id returns the ID number of the current database.
db_name	([database_id ])	Returns the database name. database_id must be a numeric expression. If no database_id is supplied, db_name returns the name of the current database.
host_id	( )	Returns the host process ID of the client process (not the Adaptive Server process).
host_name	( )	Returns the current host computer name of the client process (not the Adaptive Server process).
index_col	(object_name, index_id , key_ #[, user_id ])	Returns the name of the indexed column; returns NULL if object_name is not a table or view name. The indexed column name can be up to 255 bytes.
isnull	(expression1, expression2)	Substitutes the value specified in expression2 when expression1 evaluates to NULL.The datatypes of the expressions must convert implicitly, or you must use the convert function.
lct_admin	({{ "lastchance" \| "logfull" \| "unsuspend"} , database_id } \| "reserve", log_pages })	Manages the log segment’s last-chance threshold. lastchance creates a last-chance threshold in the specified database. logfull returns 1 if the last-chance threshold has been crossed in the specified database, or 0 if it has not. unsuspend awakens suspended tasks in the database and disables the last-chance threshold if that threshold has been crossed. reserve returns the number of free log pages required to successfully dump a transaction log of the specified size.
identity_burn_max	(table_name)	Tracks the identity burn max value for a given table. This function returns only the value; does not perform an update.
is_quiesced	(dbid)	Indicates whether a database is in quiesce database mode. is_quiesced returns 1 if the database is quiesced and 0 if it is not.
lockscheme	(object_id [, db_id])	Returns the locking scheme of the specified object as a string.
mut_excl_roles	(role1, role2 [membership \| activation])	Returns information about the mutual exclusivity between two roles.
next_identity	(table_name)	Retrieves the next identity value that is available for the next insert.
pagesize	(object_name [, index_name])	Returns the page size, in bytes, for the specified object.
object_id	(object_name)	Returns the object ID.
object_name	(object_id [, database_id ])	Returns the object name for the specified object ID. The object name can be up to 255 bytes.
Proc_role	("role_name")	Returns information about whether the user has been granted the specified role.
reserved_pgs	(object_id, {doampg \| ioampg})	Returns the number of pages allocated to table or index, including report pages used for internal structures.
rowcnt	(doampg)	Returns the number of rows in a table (estimate).
suser_id	([server_user_name])	Returns the server user’s ID number from syslogins. If no server_user_name is supplied, it returns the server ID of the current user.
suser_name	([server_user_id])	Returns the server user’s name. Server user’s IDs are stored in syslogins. If no server_user_id is supplied, it returns the name of the current user.
tsequal	(timestamp, timestamp2 )	Compares timestamp values to prevent update on a row that has been modified since it was selected for browsing. timestamp is the timestamp of the browsed row; timestamp2 is the timestamp of the stored row. Allows you to use browse mode without calling DB-Library.
tran_dumpable_status	("database_name")	Returns a true/false indication of whether dump transaction is allowed.
used_pgs	(object_id, doampg, ioampg)	Returns the total number of pages used by a table and its clustered index.
user		Returns the user’s name.
user_id	([user_name])	Returns the user’s ID number. Reports the number from sysusers in the current database. If no user_name is supplied, it returns the ID of the current user.
user_name	([user_id])	Returns the user’s name, based on the user’s ID in the current database. If no user_id is supplied, it returns the name of the current user.
valid_name	(character_expression [, maximum_length ]	Returns 0 if the character_expression is not a valid identifier, or a number other than 0 if it is a valid identifier. The second, optional parameter argument is an integer with value > zero (0) and <= 255. The default is 30. If the identifier length is larger than the maximum_length argument, valid_name returns 0—and the indentifier is invalid. See Chapter 1, “SQL Building Blocks,” for definitions of valid identifiers.
valid_user	(server_user_id)	Returns 1 if the specified ID is a valid user or alias in at least one database on this Adaptive Server. You must have the sa_role or sso_role role to use this function on a server_user_id other than your own.

When the argument to a system function is optional, the current database, host computer, server user, or database user is assumed. With the exception of user, built-in functions are always used with parentheses, even if the argument is NULL.

View this book as PDF