Collections:
sys.objects - Listing All User Defined Functions in SQL Server
How To List All User Defined Functions in the Current Database in SQL Server Transact-SQL?
✍: FYIcenter.com
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.
2016-12-24, 1181👍, 0💬
Popular Posts:
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
How to create new tables with "CREATE TABLE" statements in SQL Server? If you want to create a new t...
What Is Open Database Communication (ODBC) in Oracle? ODBC, Open Database Communication, a standard ...
What is Microsoft SQL Server in SQL Server? Microsoft SQL Server is a relational database management...
Where to find answers to frequently asked questions on SQL Transaction Management in Oracle? Here is...