Listing all sprocs in a database

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

SQL Server 2000 Forums

Transact-SQL (2000)

Listing all sprocs in a database

Author

Topic

billsox
Yak Posting Veteran

74 Posts

Posted - 2003-08-26 : 17:37:43

Hi -- How can I get a list (via T-SQL) of all of the stored procedures in a database that are not system stored procedures?

Thanks in advance,

Bill

HeatherWitt
Starting Member

8 Posts

Posted - 2003-08-26 : 17:42:33

Try this:

SELECT name FROM Sysobjects
WHERE XType = 'P'
AND Category <> 2
ORDER BY Name

billsox
Yak Posting Veteran

74 Posts

Posted - 2003-08-26 : 17:45:59

Thanks, Heather. I was hoping to avoid the system tables (as Microsoft recommends) but if there's no other way, then yours should work fine. Thanks!

quote:
Originally posted by HeatherWitt

Try this:

SELECT name FROM Sysobjects
WHERE XType = 'P'
AND Category <> 2
ORDER BY Name

srf
Starting Member

42 Posts

Posted - 2003-08-26 : 17:51:34

I think the supported method would be:

select distinct SPECIFIC_NAME
from INFORMATION_SCHEMA.Parameters

Though I don't see a way to filter out functions.

HeatherWitt
Starting Member

8 Posts

Posted - 2003-08-26 : 17:53:45

I NEVER update the system tables (as Microsoft recommends), but it can be a handy way to get information about objects in your database. There is also a system stored procedure -sp_stored_procedures - that you can use - but it includes the system stored procedures.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-26 : 18:37:21

If the INFORMATION_SCHEMA views have the information that you need, then you should query those instead of the system tables. The system tables could change with a version where the INFORMATION_SCHEMA views most likely will not. Most information that you will need will be found in the views.

Tara

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-08-26 : 19:06:29

SELECT name FROM sysobjects WHERE type='P' AND OBJECTPROPERTY(id, 'IsMSShipped')=0

This is one of those things where INFO_SCHEMA doesn't quite do the job. While Billsox's code also works, this version will probably continue to work in later versions of SQL Server.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-08-26 : 19:11:27

Here is a tip...

In "Query Analsyer".."Tools".."Customize"

Select a shortcut (mine is CTRL+3) and type in "sp_stored_procedures null, 'dbo'"

Every time you need the procs of a database just hit the short cut key...

DavidM

"SQL-3 is an abomination.."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources