In several situations, I wanted to find all user defined functions in SQL Server database along with the details like function type, created date, etc.. . I’ve done these things using simple Transact-SQL statements and also by using the SQL Server Management Studio (SSMS) object explorer GUI.
In my earlier articles, I’ve explained the various techniques to find the stored procedures as well as the methods to find tables in the database. Using similar techniques, here I’ve listed down various methods to search and find all user defined functions in a database.
Find All User Defined Functions Using Transact-SQL
To get the list of all the functions in a database, you can use the transact SQL statement against the system objects like Sys.Objects, Information_Schema.Routines, syscomments or Sys.Sql_Modules.
1. Using Sys.objects
From sys.objects system view, you can get the name, schema id, UDF type, created date time, altered date time and other details. you can used sys.object view to get the list of user defined functions in 2 ways. One way is to use the type column to filter by object types. Another way is to use the type_desc, i.e. the description of the object type to filter and get only the UDFs. Here I’ve provided the sql statements for both the methods to find all user defined fnctions.
USE WideWorldImporters; GO SELECT name AS 'Function Name', SCHEMA_NAME(schema_id) AS 'Schema', type_desc AS 'Function Type', create_date AS 'Created Date' FROM sys.objects WHERE type in ('FN', 'IF', 'FN', 'AF', 'FS', 'FT'); GO
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
Alternatively, you can use the below query with type_desc in where clause instead of using the type column. This is an ugly way of looking for UDF, but works.
USE WideWorldImporters; GO SELECT name AS 'Function Name', SCHEMA_NAME(schema_id) AS 'Schema', type_desc AS 'Function Type', create_date AS 'Created Date' FROM sys.objects WHERE type_desc LIKE '%FUNCTION%'; GO
2. Using Information_Schema.Routines
You can use the Information_Schema.Routines system view to list down all the functions in a database. From this view you can get the name, definition / source code, database schema, created and modified date, and more details of the UDF. Here is the simple query to find all the user defined functions in a database.
USE WideWorldImporters; GO SELECT ROUTINE_NAME, ROUTINE_DEFINITION , ROUTINE_SCHEMA, DATA_TYPE, CREATED FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' GO
3. Using Syscomments
You can use the syscomments system view to list the object id and the source code of any programmable objects within the database. Here is a query using syscomments to find all the functions in a database. If needed you can use OBJECT_SCHEMA_NAME(id) in the select, to get the schema name of the UDF.
USE WideWorldImporters; GO SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE OBJECTPROPERTY(id, 'IsInlineFunction') = 1 OR OBJECTPROPERTY(id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(id, 'IsTableFunction') = 1 GO
4. Using Sys.sql_modules
Just like syscomments, you can use sys.sql_modules system catalog view to list all the user defined functions. Here is the transact statement.
USE WideWorldImporters; GO SELECT DISTINCT OBJECT_NAME(object_id) AS 'Function Name', OBJECT_SCHEMA_NAME(object_id) AS 'Schema Name' FROM sys.sql_modules WHERE OBJECTPROPERTY(object_id, 'IsInlineFunction') = 1 OR OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(object_id, 'IsTableFunction') = 1 GO
Find All UDFs Using SSMS Object Explorer GUI
Another easiest way to find all the user defined function is through the SQL Server Management Studio (SSMS) Object Explorer window. In case if you are not familiar with this method, just follow the below steps.
- 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.
Please write a comment and let me know your suggestions.
- About user defined functions in MSDN.