How to get list of all stored proc names from DB

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)

How to get list of all stored proc names from DB

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2005-06-10 : 15:45:51

Is it possible to get the list of all the stored procedure names available in a database.

Please i need to get a print out of it to do some checking.

I am using sql server 2000/

And my database name is myproject

Please let me know, Thank you very much for the information.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-10 : 15:50:31

here's one way:

select name from sysObjects where type = 'P' order by name

Be One with the Optimizer
TG

EDIT:
I'm trying to stop using the system tables so here's a (newer) way:
select routine_name from information_schema.routines where routine_type = 'procedure' order by routine_name

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-06-10 : 18:57:03

Another way:

select so.name
from sysobjects so
where objectproperty(so.id, N'IsProcedure') = 1
order by so.name

TG: Would you look at the objectProperty as just a coding shortcut, is it good/bad practice to utilize them?

DBASlut
Yak Posting Veteran

71 Posts

Posted - 2005-06-10 : 19:17:33

nathans, i wouldn't look at the objectProperty as a bad practice, similar to getting the approximate record count from the sys tables instead of going directly to the table itself..:)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-10 : 19:20:58

Here is one I wrote that uses objectproperty to join a system table to an INFORMATION_SCHEMA view in order to look at the column descriptions stored in extended properties.

And, yes I know about the fn_listextendedproperty function.


select
	a.[TABLE_SCHEMA],
	a.[TABLE_NAME],
	a.[ORDINAL_POSITION],
	b.[VALUE]
from
	[INFORMATION_SCHEMA].[COLUMNS] a
	inner join
	[dbo].[sysproperties] b
	on 	a.[TABLE_SCHEMA]	=
		user_name(objectproperty(b.[id],'OwnerId'))	and
	 	a.[TABLE_NAME]		= object_name(b.[id])	and
		a.[ORDINAL_POSITION]	= b.[smallid]
where
	b.[TYPE]		= 4		and
	a.[TABLE_SCHEMA] 	= 'dbo'		and
	a.[TABLE_NAME]		= 'T_FOO'
order by
	a.[TABLE_SCHEMA],
	a.[TABLE_NAME],
	a.[ORDINAL_POSITION]

quote:
Originally posted by nathans
TG: Would you look at the objectProperty as just a coding shortcut, is it good/bad practice to utilize them?

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-11 : 10:08:39

>>TG: Would you look at the objectProperty as just a coding shortcut, is it good/bad practice to utilize them?

Not a bad practice at all. In fact, Sql Server may very well change usage, limit access, or remove entirely system tables/columns in the future. But their own functions like objectproperty should continue to return accuate results. (even if they change the way it works under the sheets)

Be One with the Optimizer
TG

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-13 : 03:24:04

SELECT DISTINCT name
FROM sysobjects
WHERE (xtype = 'p')
is also the soln!!!

Thanks,
Vivek

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-13 : 03:24:37

why the fuss abt this ?? can anyone explain !!!

Thanks,
Vivek

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-13 : 08:26:53

The fuss is that accessing system tables can cause broken code between versions. Several system tables changed from SQL 7.0 and 2000, and in 2005, ALL of the system tables are going to change. If you have a solution that uses INFORMATION_SCHEMA views or Property() functions, you avoid the potential for broken code.

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-13 : 09:00:27

OK, thanks for the valuable i/p

Thanks,
Vivek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources