Development MS SQL question

My sql knowledge can be described in the following run-on sentance "I know how to do the things that I need to do, except for when I don't know how to do them - and so I learn how to do them and then I know."

In that spirit, I was wondering if it's possible to write the following 2 queries:

10 SQLgeek points to anyone who can give me the (correct) answer.
--------------------------
Select *FunctionNames* from *AllUserDefinedFunctionsOnServer* where *FunctionName* like 'TheLookupValue%'
(and above for views, tables, stored procs, etc....)

and

Select *FunctionDefinition* from *AllUserDefinedFunctionsOnServer* where *FunctionName* ='TheFunctionName'
(again, I would also want the above for views, tables, stored procs, etc....)

 

perhaps it is just me but i don't understand what you're asking?

 

It's not. I mean i don't know anything about SQL but I do know thats a bad explantion. I'll give ten points to whoever can figure out what he means.

 

So you want to get the name and the code from the udf's on the sql server where they match your lookup value. Makes sense to me but my SQL knowledge is very similar to yours.

 

In plain english -
(Jamie got it right by the way,)

First, I want to run a query that will return the names of all the *user defined functions in the database.

Second, I want a query that will return the code of the *UDF named in the query.

Make any more sense now?

* - Or stored procs or views or etc...

 

Something like -

Code:

Select name 
from sys.all_objects 
where type_desc = 'SQL_SCALAR_FUNCTION' 
order by name

or

Code:

Select name 
from sys.all_objects 
where type_desc = 'VIEW'
order by name

That would be query 1 that I wanted above. Any ideas how I could build query 2? (That's the one that actually returns the code.)

 

well - I guess I have to give my points to me -

(Actually, I'll have to share my points with google, but it took a while.)

select CMT.text from syscomments CMT
inner join sysobjects OBJ
on CMT.id = OBJ.id
where OBJ.name = 'udf.PhoneNumClean'