Multiple Sql server queries to find all User Defined Functions (UDF’s) in a database

Introduction:  In this short article I am going to share sql server commands/statements to get or check all user defined functions i.e. the functions created by us rather than in-built functions.

 In previous article i explained How to find all or any Sql Server Table,View,Stored Procedure and Schema and  20 main differences between Stored procedures and Functions in Sql Server and Create, check and drop FOREIGN KEY CONSTRAINT and Check PRIMARY, FOREIGN, UNIQUE KEY CONSTRAINTS and Create table in sql server with auto increment primary key column

Description:  I have mentioned 3 statements/commands through which you can check all the user defined functions created under a database.

Implementation: Let’s find the UDF’s with any of the 3 commands mentioned below. On executing any of these commands you will get list of all the user defined functions created.

Command 1 

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 WHERE RIGHT(type_desc,8)='FUNCTION'

Command 2 

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

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

Command 3

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

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

Now over to you:

" I hope you have got the way to find all user defined functions created in sql server database and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linked in and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates." 

• Tweet
• Share
• Share
• Share

Related Post