System functions return special information from the server or database.
function_name(parameters)
where
These are the system functions:
| System function |
Parameters |
Result |
|---|---|---|
| COALESCE | (expression1, expression2, ... expressionN) | Returns the first non-null expression. For details, see the CASE statement. |
| COL_LENGTH | ('table_name', 'column_name') |
The defined length of a column. |
| COL_NAME | (table_id, column_id) |
The name of the column. |
| DATALENGTH | ('expression') | The actual length of an expression of any datatype. Because varchar, varbinary, text, and image datatypes can store variable-length data, DATALENGTH is especially useful with those datatypes. The DATALENGTH of any null data returns NULL. For all other datatypes, DATALENGTH reports their defined length (because NON NULL fixed-width character data is always right-padded with spaces). |
| DB_ID | (['database_name']) | The database identification number. |
| DB_NAME | ([database_id]) | The database name. |
| GETANSINULL | (['database_name']) | The default nullability for the database. This function returns 1 when the nullability is the ANSI NULL default. A column or datatype whose nullability is not explicitly defined will be set to allow null values. |
| HOST_ID | ( ) | The workstation identification number. |
| HOST_NAME | ( ) | The workstation name. |
| IDENT_INCR | ('table_or_view') | The increment value (returned as numeric(@@MAXPRECISION,0)) specified during creation of an identity column of a table or a view that includes an identity column.. |
| IDENT_SEED | ('table_or_view') | The seed value (returned as numeric(@@MAXPRECISION,0)) specified during creation of an identity column of a table or a view that includes an identity column. |
| INDEX_COL | ('table_name', index_id, key_id) |
The indexed column name. |
| ISNULL | (expression, value) | Replaces NULL entries with the specified value. |
| NULLIF | (expression1, expression2) | The resulting expression is NULL when expression1 is equivalent to expression2. For details, see the CASE statement. |
| OBJECT_ID | ('object_name') | The database object identification number. |
| OBJECT_NAME | (object_id) | The database object name. |
| STATS_DATE | (table_id, index_id) | The date that the statistics for the specified index (index_id) were last updated. |
| SUSER_ID | (['login_name']) | The user's login identification number. |
| SUSER_NAME | ([server_user_id]) | The user's login identification name. |
| USER_ID | (['user_name']) | The user's database identification number. |
| USER_NAME | ([user_id]) | The user's database username. |
When the parameter to a system function is optional, the current database, host computer, server user, or database user is assumed. Built-in functions must always be followed by parentheses.
System functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed. For more information, see the Expressions topic.
The system administrator's server user identification number is always 1. Server user identification number -1 is always reserved for a guest account. The user identification number of the database owner is always 1.
This example returns Harold's identification number, 13.
SELECT USER_ID('Harold')
This example returns the username for user number 13, Harold.
SELECT USER_NAME(13)
This example finds the average of the prices of all titles, substituting the value $10.00 for all null entries in the price column of the titles table.
SELECT AVG(ISNULL(price, $10.00))
FROM titles
This example finds the length of the title column in the titles table. The x gives a column heading to the result.
SELECT x = COL_LENGTH('titles', 'title')
This example finds the length of the pub_name column in the publishers table.
SELECT length = DATALENGTH(pub_name), pub_name
FROM publishers
This example finds the row in sysusers in which the name is equal to the result of applying the system function user_name to user identification number 1.
SELECT name
FROM sysusers
WHERE name = USER_NAME(1)
This example returns the date of the last time that the statistics were updated for the specified object (the distribution information was updated).
SELECT 'Index Name' = i.name,
'Statistics Date' = STATS_DATE(i.id, i.indid)
FROM sysobjects o, sysindexes i
WHERE o.name = 'employee' AND o.id = i.id