Find all the User Defined functions in a database using SQL Server

In this short snippet, we will be using the Catalog Views to query and display a list of UDF's created on a database.

Use this query :

USE AdventureWorks
GO
SELECT name AS [UDFName]
,create_date as [CreationDate]
,modify_date as [ModificationDate]
,type_desc as [FunctionType]
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%'
ORDER BY [UDFName]


-- Displays a list of all the UDF's on the AdventureWorks database along with the creation date, modification date and function type.

Note: User-defined functions are either scalar-valued or table-valued. Table-valued functions are further classified as inline or multi-statement.

What is the difference between Scalar-Valued, Inline Table-Valued and Multi-Statement Table-Valued functions?

Scalar functions return a single data value using the RETURNS statement. Inline table-valued functions return the result set of a single SELECT statement whereas Multistatement table-valued functions return a table.

References : http://msdn.microsoft.com/en-us/library/ms186755.aspx