Home >> FAQs/Tutorials >> SQL Server FAQ

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

By: FYIcenter.com

(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:

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...)

  1. What Are User Defined Functions?
  2. What Are the Differences between User Defined Functions and Stored Procedures?
  3. How To Create a Simple User Defined Function?
  4. How To Use User Defined Functions in Expressions?
  5. How To List All User Defined Functions in the Current Database?
  6. How To Drop an Existing User Defined Function?
  7. How To Generate CREATE FUNCTION Script on an Existing Function?
  8. How To Get the Definition of a User Defined Function Back?
  9. How To Modify an Existing User Defined Function?
  10. How To Create User Defined Functions with Parameters?
  11. How To Provide Values to User Defined Function Parameters?
  12. Can You Pass Expressions to Function Parameters?
  13. How To Provide Default Values to Function Parameters?
  14. How Many Categories of Functions based Their Return Modes?
  15. How Many Ways to Create Table-Valued Functions?
  16. How To Create an Inline Table-Valued Function?
  17. How To Create an Multi-Statement Table-Valued Function?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...