Jaa

Using System Functions

  • Artikkeli

System functions enable you to access information from the SQL Server 2005 system tables without accessing the system tables directly.

The names of some Transact-SQL system functions start 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 behavior as variables. The @@functions are system functions, and their syntax usage follows the rules for functions.

Each of the following 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, to obtain a database ID number, use the DB_ID function instead of executing a SELECT of the sysobjects table.

The following example shows how to retrieve the user name for the current user that is logged on by 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_LENGTH
    Returns the length of a column, but 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.
    The following 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

  • COL_NAME
    Returns a column name.

  • INDEX_COL
    Returns an index column name.

We recommended that you use the system functions, Information Schema Views, or the system stored procedures to obtain system information without directly querying the system tables. System tables can change significantly between versions of SQL Server.

See Also

Concepts

DEFAULT Definitions

Other Resources

System Functions (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance