where is sys.functions?
-
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.tablesselect * from sys.proceduresselect * from sys.viewsselect * 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! -
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
-
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! -
Use this one instead:
select * from sys.all_objects
where
type = 'FN' -- SQL scalar function
-
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.objectsWHERE type IN (N'FN', N'IF', N'TF');
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply