SQL Server – where is “sys.functions”?

SQL Server 2005 has great sys.XXX views on the system catalog which I use frequently.

What stumbles me is this: why is there a sys.procedures view to see info about your stored procedures, but there is no sys.functions view to see the same for your stored functions?

Doesn’t anybody use stored functions? I find them very handy for e.g. computed columns and such!

Is there a specific reason sys.functions is missing, or is it just something that wasn’t considered important enough to put into the sys catalog views? Is it available in SQL Server 2008?

Cheers, Marc

Answer

I find UDFs are very handy and I use them all the time.

I’m not sure what Microsoft’s rationale is for not including a sys.functions equivalent in SQL Server 2005 (or SQL Server 2008, as far as I can tell), but it’s easy enough to roll your own:

CREATE VIEW my_sys_functions_equivalent
AS
SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')  -- scalar, inline table-valued, table-valued