Using System Functions
System functions allow you to access information from Microsoft® SQL Server™ system tables without accessing the system tables directly.
The names of some Transact-SQL system functions begin with two at signs (@@). Although in earlier versions of SQL Server the @@functions are referred to as global variables, they are not variables and do not have the same behaviors as variables. The @@functions are system functions, and their syntax usage follows the rules for functions.
This group of five pairs of system functions for databases, hosts, objects, logins, and users returns a name when given an identifier (ID) and returns an ID when given a name:
- DB_ID and DB_NAME
- HOST_ID and HOST_NAME
- OBJECT_ID and OBJECT_NAME
- SUSER_ID and SUSER_NAME (or SUSER_SID and SUSER_SNAME)
- USER_ID and USER_NAME
For example, use the DB_ID function to get a database ID number rather than executing a SELECT of the sysobjects table.
The following example shows how to retrieve the username for the current user who is logged on (using SQL Server Authentication):
The following functions are similar, but they do not occur in complementary pairs and they take more than one input parameter:
Returns a column name.
Returns a column length.
Returns an index column name.
COL_LENGTH returns the length of a column, not the length of any individual strings stored in the column. Use the DATALENGTH function to determine the total number of characters in a specific value.
This example returns the column length and data length of the LastName column in the Employees table:
SELECT COL_LENGTH('Employees', 'LastName') AS Col_Length, DATALENGTH(LastName) AS DataLength FROM Employees WHERE EmployeeID > 6
Note It is recommended that the system functions, Information Schema Views, or the system stored procedures be used to gain access to system information without querying the system tables directly. System tables can change significantly between versions of SQL Server.