A number of built-in system functions help you query the system tables.
System functions have the following syntax:
SELECT function_name(parameters)
System functions can be used in the select list, in the WHERE clause, and anywhere else an expression is allowed.
For example, there are five pairs of system functions that, for databases, hosts, objects, logins, and users, return a name when given an ID and return an ID when given a name:
DB_ID('database_name') DB_NAME(database_id)
HOST_ID( ) HOST_NAME( )
OBJECT_ID('object_name') OBJECT_NAME(object_id)
SUSER_ID('server_username') SUSER_NAME(server_user_id)
USER_ID('username') USER_NAME(user_id)
To use these functions, combine them with the SELECT statement. These functions can also be included in the WHERE clause of a view to make the view dynamically conform to the user or environment at runtime. Similarly, they can be used in CHECK constraints.
These functions eliminate the need to join two tables when you need to convert a name to an ID or an ID to a name.
The following functions are similar, but they do not occur in complementary pairs and they take more than one input parameter:
COL_NAME(object_id, column_id)
COL_LENGTH('object_name', 'column_name')
INDEX_COL('object_name', index_id, key_id)
The COL_NAME system function returns a column name. The COL_LENGTH system function returns a column length. The INDEX_COL system function returns an index column name.
The following example shows how easy it is to write a query against the system tables when you use the system functions. For example, consider the two system tables sysobjects and sysdepends. If you know the object ID of a particular object, you can list the object IDs of all objects that depend on that object:
select id from sysdepends where depid = object_id
Here are some examples:
To find the user identification number of your coworker who logs in as harold:
select user_id('harold')
If harold's user ID number is 13, the result looks like this:
------------ |
|
13 |
|
(1 row(s) affected) |
|
Generally, as in this example, the name of the function tells you what type of information it returns.
The USER_NAME system function takes an ID number as its parameter and returns the user's name:
select user_name(13) --------- harold (1 row(s) affected)
To find the name of the current user (that is, your name), omit the parameter:
select user_name() ------ dbo (1 row(s) affected)
Built-in functions are always used with parentheses, even when there is no parameter. An exception to this is niladic functions used with DEFAULT constraints. They take no parameters. When the parameter to a system function is optional, the current database, host computer, server user, or database user is assumed.
For a complete list of system functions, see Functions in the Microsoft SQL Server Transact-SQL Reference. The following section gives a few system function examples.