user stored procedures - SQL Server Forums

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

user stored procedures

Author

Topic

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-30 : 18:08:26

is there a sql query i can type in to list all the user stored procedures for the tables within my database?

or do i have to find them manually and how?

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-11-30 : 18:42:12

ok i figured this helps
select name from sysobjects where type='P';
but it doesnt find procedures in different databases just current database/table the query is on

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-01 : 00:15:20

Don't query system tables directly, use INFORMATION_SCHEMA.ROUTINES view.

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

To get routine names in different db:

SELECT ROUTINE_NAME FROM DB2.INFORMATION_SCHEMA.ROUTINES

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-01 : 03:13:39

SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'

Madhivanan

Failing to plan is Planning to fail

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-03 : 17:38:08

thanx heaps worked:)

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-04 : 21:07:06

okay heres something thatd help, how would i select all the database names ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-04 : 23:22:42

Select name from master..sysdatabases

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-04 : 23:35:10

sp_databases

if you don't wan't to touch system tables.

Harsh Athalye
India.
"Nothing is Impossible"

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-05 : 00:30:05

how about selecting all the stored procedures not only those from withing current database

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-05 : 03:25:16

using Undocumented way:

sp_msforeachdb 'select routine_name from ?.information_schema.routines'

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-05 : 05:58:19

quote:
Originally posted by rnbguy

how about selecting all the stored procedures not only those from withing current database

Select 'SELECT ROUTINE_NAME FROM '+name+'.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE=''PROCEDURE'' from Master..sysdatabases

Madhivanan

Failing to plan is Planning to fail

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-05 : 17:42:26

the undocumented way works perfect!!!!! ur a legend

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-06 : 10:11:37

quote:
Originally posted by rnbguy

the undocumented way works perfect!!!!! ur a legend

Because it is undocumented, you shouldnt rely on that

Madhivanan

Failing to plan is Planning to fail

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-06 : 18:28:09

i wonder if there is a way to use two undocumented procedures in one line, so that i can search all tables within all databases so using
sp_msforeachdb and sp_msforeachtable in one line

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-06 : 23:35:45

Read my third reply

Madhivanan

Failing to plan is Planning to fail

rnbguy
Constraint Violating Yak Guru

293 Posts

Posted - 2006-12-11 : 21:54:35

Incorrect syntax near the keyword 'FROM'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-11 : 22:29:02

The answer to your question is No, because internally sp_MSForEachDB and sp_MSForeachTable uses same cursor name.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources