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?

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 — Maashu, Dec 01 '09 at 22:55
@JuniorMayhe: ok - here's the [Connect suggestion feedback](https://connect.microsoft.com/SQLServer/feedback/details/1127918) that I entered - go upvote it! :-) — marc_s, Feb 13 '15 at 12:09
I think @marc_s has a good point: many people cannot understand why there is no `sys.functions`. You have `sys.foreign_keys` and no `sys.primary_keys`. Anyway, I ask you guys to use Microsoft's open channel to propose and suggest new features for upcomming versions of SQL Server at https://connect.microsoft.com/SQLServer/Feedback I have already added a feedback regarding sys.functions at https://connect.microsoft.com/SQLServer/feedback/details/1127920 — Junior Mayhé, Feb 13 '15 at 12:12

score 131 · Accepted Answer · edited Feb 13 '15 at 12:09

131

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

edited Feb 13 '15 at 12:09

marc_s

732,580
175
1,330
1,459

answered Jan 22 '09 at 11:35

LukeH

263,068
57
365
409

8

This should also include the CLR function types: 'AF', 'FS', and 'FT'. See sys.objects "type" column description here: http://msdn.microsoft.com/en-us/library/ms190324.aspx – Triynko Aug 21 '11 at 06:28
4

"AF" is not considered a "function" in terms of SQL Server's object metadata even though it stands for AGGREGATE_FUNCTION. It is more clear that an Aggregate is an object type different from other user-defined functions when considering that you create a new aggregate using CREATE AGGREGATE instead of CREATE FUNCTION. Object types 'FN', 'IF', 'TF', 'FS' and 'FT' are the five function types per what SSMS (via SMO) generates when scripting IF EXISTS...DROP FUNCTION code. – Orlando Colamatteo Dec 21 '12 at 10:14

Tim C · Answer 2 · 2009-01-22T14:41:38.277

39

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.

edited Jan 22 '09 at 14:41

answered Jan 22 '09 at 14:06

Tim C

70,053
14
74
93

Yes, thanks, I do know the INFORMATION_SCHEMA, too - but as a long time user, sys.xxxx still comes easier - thanks for the reminder! – marc_s Jan 22 '09 at 17:35
4

INFORMATION_SCHEMA would be great, but it doesn't include the full body of larger procedures -- which makes it less than worthless if you're searching in the body. It's not what you don't know that will get you in trouble, but what you know that aint so... – jmoreno Feb 10 '12 at 18:40
3

Information_Schema views are expressly documented as not being reliable for some things. e.g. "Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the schema of a object is to query the sys.objects catalog view. " from http://msdn.microsoft.com/en-us/library/ms188757.aspx – David Eison Mar 08 '12 at 00:17
I like this answer, because the `INFORMATION_SCHEMA` query returns very interesting results like `IS_DETERMINISTIC` (which I wanted to find out). – Tomasz Gandor Nov 03 '15 at 07:09

score 21 · Answer 3 · edited Mar 04 '16 at 08:59

21

This is valid in 2008 R2 per what SSMS generates when you script a DROP of a function:

SELECT  *
FROM    sys.objects
WHERE   type IN (N'FN', N'IF', N'TF', N'FS', N'FT') ;

/*
From http://msdn.microsoft.com/en-us/library/ms177596.aspx:
 FN SQL_SCALAR_FUNCTION
 FS Assembly (CLR) scalar-function
 FT Assembly (CLR) table-valued function
 IF SQL_INLINE_TABLE_VALUED_FUNCTION
 TF SQL_TABLE_VALUED_FUNCTION
*/

edited Mar 04 '16 at 08:59

Orlando Colamatteo

97
9

answered Mar 01 '11 at 18:37

Orlando Colamatteo

990
8
9

1

Your edit suggestion should be a comment, not an edit. the "AF Aggregate function" is clearly from the MS documentation (check the link), so this post looks entirely correct. If you disagree: **comment**. Not edit. If people repeatedly rejected your edit this should be a hint that *you* are perhaps doing something wrong, and *not* the other people. – Martin Tournoij Mar 04 '16 at 01:30
@Carpetsmoker "AF" is not considered a "function" in terms of SQL Server's object metadata even though it stands for AGGREGATE_FUNCTION. It is more clear that an Aggregate is an object type different from other user-defined functions when considering that you create a new aggregate using CREATE AGGREGATE instead of CREATE FUNCTION. Object types 'FN', 'IF', 'TF', 'FS' and 'FT' are the five function types per what SSMS (via SMO) generates when scripting IF EXISTS...DROP FUNCTION code. You should accept my edit to revert the incorrect addition of AF to the list of SQL Server function types. – Orlando Colamatteo Mar 04 '16 at 08:45

score 5 · Answer 4 · edited Jan 29 '16 at 13:46

5

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 Server 2008 either.

edited Jan 29 '16 at 13:46

marc_s

732,580
175
1,330
1,459

answered Jan 22 '09 at 11:35

Ayresome

119
2

1

Yes, that's what I basically did myself to create a "sys_functions" view :-) Just wondering why it's not in the product out of the box.... – marc_s Jan 22 '09 at 17:34

score 4 · Answer 5 · edited Jan 27 '14 at 10:22

4

try this :

SELECT * FROM sys.objects
where type_desc = 'SQL_SCALAR_FUNCTION'

edited Jan 27 '14 at 10:22

Alexis Pigeon

7,423
11
39
44

answered Jan 27 '14 at 10:02

vishal kadam

41
1

score 4 · Answer 6 · edited May 23 '11 at 12:08

4

This does not add anything new, but I found the following easier to remember:

select * from sys.objects where type_desc like '%fun%'

edited May 23 '11 at 12:08

marc_s

732,580
175
1,330
1,459

answered May 23 '11 at 11:16

yoniLavi

2,624
1
24
30

If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s May 23 '11 at 12:08
Thanks, but I try to avoid the "have-it-all" `sys.objects` as much as I can. – marc_s May 23 '11 at 12:08

score 2 · Answer 7 · answered Jul 07 '09 at 11:26

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

Peter Brand · Answer 8 · 2014-03-27T18:33:37.643

For a fuller description of scalar functions including owner and return type:

SELECT f.name, s.name AS owner, t.name as dataType, p.max_length, p.precision, p.scale, m.definition
FROM sys.objects f
JOIN sys.schemas s on s.schema_id = f.schema_id
JOIN sys.parameters p on p.object_id = f.object_id AND p.parameter_id = 0
JOIN sys.types t ON t.system_type_id = p.system_type_id 
JOIN sys.sql_modules as m ON m.object_id = f.object_id
WHERE type='FN';

score 2 · Answer 9 · answered Mar 08 '12 at 15:01

To extend upon @LukeH's answer, to return the function definitions as well requires a join to the sys.sql_modules table. So the query for this is:

SELECT O.name as 'Function name', M.definition as 'Definition', O.object_id
FROM sys.objects as O INNER JOIN sys.sql_modules as M
    ON O.object_id = M.object_id
WHERE type IN ('FN', 'IF', 'TF')  -- scalar, inline table-valued, table-valued

where the above displays the function name, its definition and the object identifier respectively.

score 0 · Answer 10 · edited Feb 29 '16 at 00:10

0

SQL 2000 specific, slight adjustment for the object name:

SELECT *
FROM sysobjects
WHERE type IN ('FN', 'IF', 'TF')

OR

SELECT *
FROM dbo.sysobjects
WHERE type IN ('FN', 'IF', 'TF')

edited Feb 29 '16 at 00:10

developer033

24,267
8
82
108

answered Jun 03 '15 at 00:06

Goran B.

542
4
14

SQL Server - where is "sys.functions"?

10 Answers10

Linked