June 15, 2006 at 12:44 am
Is their any sql to find the list of sp created by user's in master db?
------------
Prakash Sawant
http://psawant.blogspot.com
June 15, 2006 at 4:39 am
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.
June 15, 2006 at 6:28 am
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
June 16, 2006 at 7:30 am
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 !!!**
June 16, 2006 at 8:03 am
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
June 16, 2006 at 8:10 am
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 !!!**
June 16, 2006 at 8:15 am
well a small addition to sushila query
<>0 and name not like 'dt%'
June 16, 2006 at 8:24 am
This query should work in best way....
SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY(id,'IsMSShipped')=0
AND xtype = 'P'
------------
Prakash Sawant
http://psawant.blogspot.com
June 16, 2006 at 8:27 am
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
June 16, 2006 at 8:49 am
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...
**ASCII stupid question, get a stupid ANSI !!!**
June 16, 2006 at 9:02 am
Sushila,
Thanks for the link, it was nice to read that.
------------
Prakash Sawant
http://psawant.blogspot.com
June 16, 2006 at 12:17 pm
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply