Sql Server - where is "sys.functions"?

Create a custom sql.functions

SQL Server might not provide an explicit sys.functions, but you can definitely create your own. Use this script:

-- "Because if you are not, why should sys.functions be?"
CREATE VIEW my_sys_functions AS
SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF', 'FS', 'FT');


This makeshift toolbox effectively gives you the convenience of sys.functions.

Trust the INFORMATION_SCHEMA

When it comes to version independence and stability, INFORMATION_SCHEMA.ROUTINES has your back:

-- "Peter Parker... I mean, INFORMATION_SCHEMA, to the rescue"
SELECT ROUTINE_NAME, ROUTINE_TYPE 
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_TYPE = 'FUNCTION';


This moored ship never fears the high tide of system table updates.

Tap into sys.objects, unlock functions info

Need detailed Metadata for computed columns or index-related jobs? sys.objects is a goldmine:

-- "Function hunt begins here"
SELECT *
FROM sys.objects
WHERE type IN ('TF', 'FN');


A simpler option? Try using type_desc as your filter weapon:

-- "Because simplicity is the ultimate sophistication"
SELECT *
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';


These illustrate sys.objects' versatility for a range of query functions.

Visualization

Searching for something specific in a wingding collection, that's SQL Server for you:

🧰 sys.tables -> 🔨 Hammers (🔨, 🔨, 🔨)
🧰 sys.views -> 🔍 Magnifying Glasses (🔍, 🔍)
🧰 sys.procedures -> ⚙️ Gears (⚙️, ⚙️, ⚙️, ⚙️)
🧰 sys.functions -> ❓ Missing... (🕵️‍♂️ Where are the wrenches?)

Each drawer in this massive toolbox is a system catalog view, but the functions drawer is conspicuously missing.

In truth, 💡 you should aim for the Pandora's box of all tools in "sys.objects":
🧰 sys.objects -> 🛠️ Mixed Tools (🔨, 🔍, ⚙️, 🔧, ...)

The wrenches (🔧), aka sys.functions, are mingling with the other tools in the sys.objects toolbox.

Why so mystical, sys.functions?

The absence of sys.functions is like a mystery novel missing the final pages. While it highlights systematic significance, the wealth of data in sys.objects keeps the spotlight on stored functions.

Why custom view?

Creating a custom view is like getting a tailored suit. It's not about emulating sys.functions, but enhancing your querying experience by providing a comfortable and familiar API.

Scalar and Table-valued functions

Functions in SQL Server can either be scalar, yielding a lone value, or table-valued, delivering a table. Grasping this difference is crucial when designing queries, as performance and applications differ markedly between them.