May 06, 2008

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
SELECT name AS [UDFName]
,create_date as [CreationDate]
,modify_date as [ModificationDate]
,type_desc as [FunctionType]
FROM sys.objects

-- 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 :

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

No comments: