System functions that return database information

The system functions return special information from the database. Many of them provide a shorthand way of querying 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.

Table 11-1 lists the name of each system function, the argument it takes, and the result it returns.

**Table 11-1: System functions, arguments, and results**
Function	Argument	Result
col_name	(object_id, column_id [, database_id ])	Returns the column name.
col_length	(object_name, column_name)	Returns the defined length of column. Use datalength to see the actual data size.
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_pgs	([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.
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.
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.
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.
object_id	(object_name)	Returns the object ID.
object_name	(object_id [, database_id ])	Returns the object name.
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.
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)	Returns 0 if the character_expression is not a valid identifier (illegal characters or more than 30 bytes long), or a number other than 0 if it is a valid identifier.
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.