System functions perform operations and return information about database objects in SQL Server. In this article, you will see some of the SQL Server System functions which provide information about database objects. The System Functions can never be created by the user. They are pre-defined functions. So let's take a look at a practical example of how to use System Functions in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio. Some of SQL Server system functions are described in the following.
DataLength Function
The Datalength() function returns the length of the result of the string expression. It is similar to the LEN function.
Syntax
DATALENGTH(string_expression)
Example
Declare @string varchar(20)
Declare @stringname varchar(40)
set @string ='rohatash'
set @stringname ='Satendrta singh'
select DATALENGTH (@string) as StringLength
select DATALENGTH (@stringname) as StringLength
Output
![Datalength-function-in-sql-server.jpg]()
DB_NAME Function
DB_NAME Function returns the name of the database with the identifier db_id. If no identifier is specified, the name of the current database will be displayed.
Syntax
DB_NAME([db_id])
In the preceding syntax, db_id is the database identifier.
Example
Select DB_NAME() as NameofDatabase
Select DB_NAME(9) as NameofDatabase -- DB_Name with identifier
![DBname-function-in-sql-server.jpg]()
Host_Name Function
The Host_Name Function returns the name of the host or computer name.
Syntax
HOST_NAME()
Example
Select host_name() as NameofHost
Output
![Host_Name-function-in-sql-server.jpg]()
APP_NAME Function
The APP_NAME() function returns a string with the name of the application that initiated the database connection. APP_NAME() can be helpful if you're troubleshooting a connection.
Syntax
APP_NAME()
Example
Select APP_name() as Nameofapplication
Output
![Appt_Name-function-in-sql-server.jpg]()
Some Important system error functions in SQL Server
ERROR_LINE Function
The Error_Line function returns the error Line number from code. This function does not accept any parameters. Error_Line() function is used to determine the error line which occurred in a try block. The following query returns the line number where the error occurred:
BEGIN TRY
Select 11/ 0
END TRY
BEGIN CATCH
SELECT 'An error has occurred at line ' + cast(ERROR_line() as Varchar )
END CATCH
Output
![Error_Line-function-in-sql-server.jpg]()
ERROR_NUMBER Function
The ERROR_NUMBER function returns the number of the error which caused the CATCH block of TRY or CATCH logic to execute. The following query returns the number where the error occurred:
Example
BEGIN TRY
Select 11/ 0
END TRY
BEGIN CATCH
SELECT 'An error has occurred at line: ' + cast(ERROR_Number() as Varchar )
END CATCH
Output
![Host_Number-function-in-sql-server.jpg]()
ERROR_STATE Function
The ERROR_STATE function returns the state of the error which caused the CATCH block of TRY or CATCH logic to execute. The following query returns the State where the error occurred:
Example
BEGIN TRY
Select 11/ 0
END TRY
BEGIN CATCH
SELECT 'An error has occurred at line: ' + cast(ERROR_State() as Varchar )
END CATCH
Output
![Error_State-Function-in-sql-server.jpg]()
ERROR_SEVERITY Function
The ERROR_SEVERITY function returns the severity of the error which caused the CATCH block of TRY or CATCH logic to execute. The following query returns the severity where the error occurred:
Example
BEGIN TRY
Select 11/ 0
END TRY
BEGIN CATCH
SELECT 'An error has occurred at line: ' + cast(ERROR_Severity() as Varchar )
END CATCH
Output
![Error_Severity-function-in-sql-server.jpg]()