SQL Server - where is "sys.functions" ??

Question

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 1

It's very slightly more verbose, but this should do exactly the same thing:

select * from sys.objects where (type='TF' or type='FN')

As far as I can see, it's not in SQL 2008 either.

Answer 2

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 SQL2005 (or SQL2008, 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

Answer 3

Another way to list functions is to make use of INFORMATION_SCHEMA views.

SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION'

According to the Microsoft web site "Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables". In other words, the underlying System tables may change as SQL gets upgraded, but the views should still remain the same.

Answer 4

incidentally, wouldn't you want to include type = 'FS'?

name    type type_desc
getNewsletterStats  FS CLR_SCALAR_FUNCTION

that's what the item in sys.objects corresponds with for my UDF which is derived from an external DLL

Answer 5

The answer provided by TimC (answered Jan 22 at 14:06) is preferred over using the older sysobjects system table, because you've got a LAST_ALTERED column in INFORMATION_SCHEMA.ROUTINES that is similar to the modify_date column that exists in sys.tables, sys.views, sys.procedures, etc.

However, if you're using the more updated sys.objects system view, you have modify_date as in those tables.

My $0.02.

Cheers,

-Matthew