Home >> FAQs/Tutorials >> SQL Server FAQ
SQL Server FAQ - sys.objects - Listing All User Defined Functions
(Continued from previous topic...)
How To List All User Defined Functions in the Current Database?
If you want to see a list of all user defined functions in your current database,
you can use the system view, sys.objects as shown in this tutorial exercise:
-- Number of Sundays in this year
CREATE FUNCTION Sundays()
DECLARE @date DATETIME;
DECLARE @count INT;
SET @date = '2006-12-31';
SET @count = 0;
WHILE DATEPART(YEAR, @date) <= 2008 BEGIN
SET @date = DATEADD(DAY, 1, @date);
IF DATENAME(WEEKDAY, @date) = 'Sunday'
SET @count = @count + 1;
SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION';
name object_id schema_id type type_desc
-------- ----------- ---------- ---- -------------------
Welcome 2085582468 1 FN SQL_SCALAR_FUNCTION
Sundays 2117582582 1 FN SQL_SCALAR_FUNCTION
(2 row(s) affected)
sys.objects contains all types of objects in the current database.
You need select only the FUNCTION object type.
(Continued on next topic...)
- What Are User Defined Functions?
- What Are the Differences between User Defined Functions and Stored Procedures?
- How To Create a Simple User Defined Function?
- How To Use User Defined Functions in Expressions?
- How To List All User Defined Functions in the Current Database?
- How To Drop an Existing User Defined Function?
- How To Generate CREATE FUNCTION Script on an Existing Function?
- How To Get the Definition of a User Defined Function Back?
- How To Modify an Existing User Defined Function?
- How To Create User Defined Functions with Parameters?
- How To Provide Values to User Defined Function Parameters?
- Can You Pass Expressions to Function Parameters?
- How To Provide Default Values to Function Parameters?
- How Many Categories of Functions based Their Return Modes?
- How Many Ways to Create Table-Valued Functions?
- How To Create an Inline Table-Valued Function?
- How To Create an Multi-Statement Table-Valued Function?