Article

Find All The User Defined Functions /UDF In SQL Server Database

26 May 2018 Abhimanyu Gautam
0 Comments 216 Views



Today we explain the way to find the user defined function (UDF) with in a database.

1. Using Sys.Objects

Select name AS function_name,SCHEMA_NAME(schema_id) AS schema_name,create_date,modify_date,type_desc 
FROM sys.objects Where Type In ('FN', 'IF', 'FN', 'AF', 'FS', 'FT')

In The Above Statement The Object Types Of Functions Are:
FN = SQL scalar function
IF = SQL inline table valued function
TF = SQL table valued function
AF = CLR aggregate function
FS = CLR scalar function
FT = CLR table valued function

SELECT name AS function_name,SCHEMA_NAME(schema_id) AS schema_name,create_date,modify_date,type_desc 
FROM sys.objects WHERE type_desc LIKE '%FUNCTION%'

2. Using Information_Schema.Routines

SELECT ROUTINE_NAME,ROUTINE_DEFINITION,ROUTINE_SCHEMA,DATA_TYPE,CREATED
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

3. Using Syscomments

SELECT DISTINCT OBJECT_NAME(id) 
FROM Syscomments WHERE
OBJECTPROPERTY(id, 'IsInlineFunction') = 1 OR
OBJECTPROPERTY(id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(id, 'IsTableFunction') = 1

4. Using Sys.Sql_modules

SELECT DISTINCT OBJECT_NAME(object_id) AS 'Function Name'
FROM sys.sql_modules WHERE
OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1 OR
OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(object_id, 'IsTableFunction') = 1

5. Using SSMS Object Explorer GUI

  • In the Object Explorer in SQL Server Management Studio, go to the database and expand it.
  • Expand the Programmability folder.
  • Expand the Functions folder. Under the function folder, you can find sub folders for each type of UDF. Expand the sub folders to list all the user defined functions.

Comments

No coments found to display!

Leave a Comment