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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook