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.
Some system function takes more than one value as input parameters to returns output
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
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’
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