SQL Server FAQ - sys.objects - Listing All User Defined Functions

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

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:

USE FyiCenterData;
GO

-- Number of Sundays in this year
CREATE FUNCTION Sundays()
   RETURNS INT
   AS BEGIN
      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;
         END;
      RETURN @count;
      END;
GO


SELECT * FROM sys.objects WHERE type_desc LIKE '%FUNCTION';
GO
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...)