Export (0) Print
Expand All

Using System Functions

SQL Server 2000

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

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.

See Also

System Functions

ALTER TABLE

DEFAULT Definitions

CREATE TABLE

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft