How To List All User Defined Functions in the Current Database in SQL Server Transact-SQL?

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 a given year
CREATE FUNCTION Sundays()
   RETURNS INT
   AS BEGIN
      DECLARE @date DATETIME;
      DECLARE @count INT;
      SET @date = '2017-12-31'; 
      SET @count = 0;
      WHILE DATEPART(YEAR, @date) <= 2018 BEGIN
         SET @date = DATEADD(DAY, 1, @date);
         IF DATENAME(WEEKDAY, @date) = 'Sunday' 
            SET @count = @count + 1;
         END;
      RETURN @count;
      END;

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.

 

⇒Using User Defined Functions in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials