list of user sp from master 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

General SQL Server Forums

New to SQL Server Programming

list of user sp from master db

Author

Topic

psawant
Starting Member

14 Posts

Posted - 2006-06-15 : 02:43:17

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-15 : 05:51:20

[code]select name from master..sysobjects where xtype = 'P' and status > 0[/code]

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-06-15 : 05:57:26

What does the status column contain? It's NOT in BOL.

How about, the following...
[CODE]
SELECT *
FROM sysobjects
WHERE OBJECTPROPERTY(id,'IsMSShipped')=0
AND xtype = 'P'
[/CODE]

------------------------
I think, therefore I am - Rene Descartes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-15 : 06:06:10

Note that you need to omit the procedure usp_osqlexecute

Madhivanan

Failing to plan is Planning to fail

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-06-16 : 02:25:22

quote:
Originally posted by madhivanan
...procedure usp_osqlexecute

Madhivanan, what is the use of above SP?

------------------------
I think, therefore I am - Rene Descartes

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 02:38:35

I dont know the usage. See the code
sp_helptext usp_osqlexecute

Madhivanan

Failing to plan is Planning to fail

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2006-06-16 : 02:55:00

Madhivanan,
usp_osqlexecute is NOT a system SP! Check the link..
http://www.codeproject.com/database/Free_Remote_SQL_manager.asp

------------------------
I think, therefore I am - Rene Descartes

psawant
Starting Member

14 Posts

Posted - 2006-06-16 : 03:15:29

Query given by Ravi Lobo & khtan both worked

Thanks a lot for responding to my query.

psawant
Starting Member

14 Posts

Posted - 2006-06-16 : 05:08:53

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 05:11:36

quote:
Originally posted by ravilobo

Madhivanan,
usp_osqlexecute is NOT a system SP! Check the link..
http://www.codeproject.com/database/Free_Remote_SQL_manager.asp

------------------------
I think, therefore I am - Rene Descartes

Thanks. But it is system procedure shown as user defined procedure so as in the case dtproperties table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources