query to give the names of all proc and func

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query to give the names of all proc and func

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-20 : 08:01:52

Hi all,

I want to write a query to give all the names of only procedure and functions ( user deveoped not system defined) names from the database.

is the below query rt? and how can I add the schema_name to this

SELECT a.name
FROM sys.sysobjects a
WHERE xtype = 'P'
ORDER BY 1

-Neil

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-20 : 08:22:29

How about

Select [name] from sys.procedures

Harsh Athalye
http://www.letsgeek.net/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-20 : 08:49:14

how can I add schema name ? to this we have uid

Select [name] from sys.procedures

-Neil

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-20 : 08:53:49

Select schema_name(schema_id), [name] from sys.procedures

Harsh Athalye
http://www.letsgeek.net/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-20 : 09:05:48

Thanks Harsh,

I'll try this, thanks again.

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-20 : 09:17:27

Can I also include some more information like wether it is function/ procedure?

-Neil

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-05-20 : 09:56:02

This view only returns information about stored procedures, not function.

Harsh Athalye
http://www.letsgeek.net/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-20 : 11:24:50

hmm then is there any way of getting the functions as well

-Neil

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-05-20 : 11:27:21

will the below gives me proc and function?

SELECT schema_name(uid),a.name
FROM sys.sysobjects a
WHERE xtype = 'P' and a.name not like '%$%'
ORDER BY 1

-Neil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources