MS SQL Server Interview Question:

How To List All User Defined Functions in the Current Database?

Submitted by: Administrator

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 GlobalGuideLineDatabase;
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
<pre>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)</pre>
sys.objects contains all types of objects in the current database. You need select only the FUNCTION object type.
Submitted by: Administrator