How to list user-defined stored procedures? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server Development (2000)

How to list user-defined stored procedures?

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-05 : 08:53:06

Buddies,
I use the following script to list all stored procedures in a database. However, there are some system stored procedures which are listed ,too like dt_setpropertybyid, dt_dropuserobjectbyid. Please tell me how I can list only user-created stored procedures.

select * from sysobjects where type='p'

Thank you in advance.
johnsql

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-05 : 09:51:10

Use something like this:


SELECT sysobjects.name
FROM sysobjects 
WHERE type in ('P') and left(sysobjects.name,2) not in   ('sy','dt')

I have a procedure that goes through and gets all user object, columns, parameters and such...and this is the way I filtered out the non-user sprocs

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-05 : 10:06:39

quote:
Originally posted by dataguru1971

Use something like this:

SELECT sysobjects.name
FROM sysobjects
WHERE type in ('P') and left(sysobjects.name,2) not in ('sy','dt')

I have a procedure that goes through and gets all user object, columns, parameters and such...and this is the way I filtered out the non-user sprocs

Poor planning on your part does not constitute an emergency on my part.

Out of curiosity i'm asking, what will happen if we have a user written sp with a name starting with 'sy' like synchronisedates? Wont it be excluded?

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-05 : 10:11:06

quote:
Originally posted by visakh16

quote:
Originally posted by dataguru1971

Use something like this:

SELECT sysobjects.name
FROM sysobjects
WHERE type in ('P') and left(sysobjects.name,2) not in ('sy','dt')

I have a procedure that goes through and gets all user object, columns, parameters and such...and this is the way I filtered out the non-user sprocs

Poor planning on your part does not constitute an emergency on my part.

Out of curiosity i'm asking, what will happen if we have a user written sp with a name starting with 'sy' like synchronisedates? Wont it be excluded?

Yes, I agree visakh16

if I use my own stored procedure names starting with 'dt_' or 'sy_', the problem still stays there.

So, is there a better way to solve this problem?
Thanks.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-05 : 10:24:10

try this:

select * from sysobjects where type='P' and OBJECTPROPERTY(id, N'IsMSShipped') = 0

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-05 : 10:27:40

Hey, it was just a suggestion. Naming conventions aren't my problem...LOL...the IsMSShipped = 0 is a better method...

In my case, I am the only one who writes any data to my server, so my naming conventions by rule don't include sp_, sys,dt_ or other combinations which is why I wouldn't run into the issue presented..

Cheers.

Poor planning on your part does not constitute an emergency on my part.

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-05 : 11:01:11

quote:
Originally posted by jezemine

try this:

select * from sysobjects where type='P' and OBJECTPROPERTY(id, N'IsMSShipped') = 0

elsasoft.org

Thanks, this is perfect.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-06 : 21:27:05

[code]
select
ROUTINE_NAME
from
INFORMATION_SCHEMA.ROUTINES
where
ROUTINE_TYPE = 'PROCEDURE'
[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources