Using System Functions
System functions allow you to access information from Microsoft® SQL Server™ system tables without accessing the system tables directly.
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):
SELECT SUSER_NAME()
The following functions are similar, but they do not occur in complementary pairs and they take more than one input parameter:
- COL_NAME
Returns a column name.
- COL_LENGTH
Returns a column length.
- INDEX_COL
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.