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


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

0 Responses to "Find all the User Defined functions in a database using SQL Server"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions