list of user sp''s from master db

Question

Post reply

list of user sp''s from master db

Prakash Sawant

SSCrazy

Points: 2173
More actions
June 15, 2006 at 12:44 am

#60970

Is their any sql to find the list of sp created by user's in master db?

------------
Prakash Sawant
http://psawant.blogspot.com

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Eshwaran SSC Veteran Points: 229 More actions · Answer 1

Hi Prakash,

Below query will provide all the sp in the database. May be to work on this to get what exactly u need.

select * from sysobjects where type='p'

In Enterprise SP option you can type column as a user.

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 2

This will give me all the sp's along with system sp's. I want only sp's created by user's.

Thanks for the reply.

------------
Prakash Sawant

http://psawant.blogspot.com

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

If your users have a naming convention that's different from that used by SQL Server you can use that as a filter.

**ASCII stupid question, get a stupid ANSI !!!**

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 4

Following is the code i got of getting my result.

SELECT name FROM master..sysobjects WHERE xtype = 'P' and status > 0

------------
Prakash Sawant

http://psawant.blogspot.com

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

well - "status" is reserved for internal use by SQL Server - in my master db all the status values are -214######, -107##### etc. for the objects...so you may want to change that query to "status 0"!!!!!

**ASCII stupid question, get a stupid ANSI !!!**

Maverick100877 SSCertifiable Points: 5240 More actions · Answer 6

well a small addition to sushila query

<>0 and name not like 'dt%'

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 7

This query should work in best way....

SELECT *

FROM sysobjects

WHERE OBJECTPROPERTY(id,'IsMSShipped')=0

AND xtype = 'P'

------------
Prakash Sawant

http://psawant.blogspot.com

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 8

I found one more way to get the result, but I am not sure will this give the perfect result all the time.

select [name] from sysobjects where xtype ='P' and category = 0

Any suggestions on above query?

------------
Prakash Sawant

http://psawant.blogspot.com

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

Prakash - here's another poster who seems to be attempting something identical..maybe you could read up on the documentation link posted on that thread...

sysobjects.category

**ASCII stupid question, get a stupid ANSI !!!**

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 10

Sushila,

Thanks for the link, it was nice to read that.

------------
Prakash Sawant

http://psawant.blogspot.com

mel-260965 SSC Enthusiast Points: 147 More actions · Answer 11

Can't you just sort the object listing by creation date in SQL EM? All of the user created procs will bubble to one end of the list because they were created after the creation of the system procs.

...Mel