where is sys.functions?

Question

where is sys.functions?

Lowell

SSC Guru

Points: 323518
More actions
April 14, 2010 at 7:11 am

#217869

I was writing some sqls that was pulling out object names from the meta data:
it's pretty intuitive to find stuff:
select * from sys.tables
select * from sys.procedures
select * from sys.views
select * from sys.functions --fails! no such view exists!
I scrolled thru all the available sys.* stuff, and did not find a view listing just functions;
am i just stuck with hitting sys.objects with a WHERE statement, or did i overlook something?:
select * from sys.objects where type_desc IN('SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

You're not imagining it, there isn't one.

Not entirely sure of the rationale, but I guess there's an argument that they had to stop somewhere and just picked the most commonly used objects to provide a separate view for.

If it bugs you, you could always create your own view 😀

Lowell SSC Guru Points: 323518 More actions · Answer 2

Well at least it's not me;

they can make a views for the oh so heavily queried sys.routes and sys.schemas but not a sys.functions;

seems more like an oversight rather than a planned decision.

whew~!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

r.dragoi-1095738 SSCrazy Points: 2022 More actions · Answer 3

Use this one instead:

select * from sys.all_objects

where

type = 'FN' -- SQL scalar function

Paul White SSC Guru Points: 150467 More actions · Answer 4

Lowell (4/14/2010)
...am i just stuck with hitting sys.objects with a WHERE statement, or did i overlook something?

Yep, you're stuck with hitting sys.objects with a WHERE clause.

select * from sys.objects where type_desc IN('SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')

You're missing multi-statement TVFs there.

SELECT *

FROM sys.objects

WHERE type IN (N'FN', N'IF', N'TF');

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi