Find User Defined Functions (UDF’s) in a database or List all UDFs in database

Introduction:  

In case of multiple user defined function in database, it’s difficult to find a particular function or to list all the user-defined function manually.

In this article I have explained different requirements that may arise with solution while searching for user-defined functions.

Implementation: Let’s find the User-Defined Functions with three different commands as mentioned below. I have discussed three different cases.

Case 1: List All UDFs

SELECT * FROM sys.objects WHERE RIGHT(type_desc,8)='FUNCTION'

SELECT name as [Function Name],SCHEMA_NAME(schema_id) as [Schema Name] FROM sys.objects WHERERIGHT(type_desc,8)='FUNCTION'

Query 2: Find function with like operator

SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name as [Function Name],SCHEMA_NAME(schema_id) as [Schema Name] FROM sys.objects WHEREtype_desc LIKE '%FUNCTION%'

Query 3: Find Particular function if name of the function is known

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION' 

SELECT SPECIFIC_NAME as [Function Name], SPECIFIC_SCHEMA AS [Schema Name] FROMINFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE='FUNCTION'