SQL Server – System function to access system table information

SYSTEM FUNCTION

System function is basically use to access system tables of SQL Server, system functions starts with @@ sign which sometime refer to as global system variable.  It doesn’t follows variable functionality as it is more similar to function behavior.

Some commonly used system functions in sql server are as follows

DB_ID(name) and DB_NAME(ID)

HOST_ID(name) and HOST_NAME(ID)

OBJECT_ID(name) and OBJECT_NAME(ID)

SUSER_ID(name) and SUSER_NAME(ID)

USER_ID(name) and USER_NAME(ID)

These system functions related to database, hosts, objects, logins and users returns id and name.To find out the database id, you have to provide database name as input parameter to DB_ID function this way you have avoid executing select on sysobjects table.

Example: SELECT DB_ID (database_name), DB_NAME (database_id)

Below is the example of another system function to get the current user name by using sql server authentication.

SELECT SUSER_NAME()

Some system function takes more than one value as input parameters to returns output

COL_LENGTH

It returns length of the column used in the table. Consider department as table name and Dept_name is column name in below example.

SELECT COL_LENGTH(‘Department’, ‘Dept_Name’) AS Column Length

DATALENGTH

To find out total length of character stored in the column by using DATALENGTH function.

Example: SELECT DATALENGTH(dept_name) FROM Department WHERE empid = ‘1101’

 

COL_NAME

It returns column name from table.

Example: SELECT COL_NAME(OBJECT_ID(‘dbo.Department’), 1) AS ‘Column Name’;

Here first input parameter is table_id and second input parameter is column id

You can use these system functions to get the sql server information

Leave a Reply

  

  

  


− 1 = 3