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 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

These functions provide an easy way to convert a name to an ID or an ID to a name. For example, use the DB_ID function to get a database ID number rather than executing a SELECT of the sysobjects table.

This example retrieves the username for the current user who is logged in (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 System 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.

See Also