system stored procedures in SQL servre 2000

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

Transact-SQL (2000)

system stored procedures in SQL servre 2000

Author

Topic

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2006-02-02 : 02:25:04

hi

How can get the list of system stored procedures in SQL server 2000?
I know that all the stored procedures which begin with sp_ are system sps. but what are the main stored procedure between them?
for Example, the sp which lists the user defined sp,...etc?

Thank you

sonia

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-02 : 02:30:40

Look in Books Online under Transact-SQL Reference - System Stored Procedure

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-02 : 03:44:12

>>the sp which lists the user defined sp

Select name from sysobjects where xtype='p'

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-02 : 05:02:54

quote:
Originally posted by madhivanan

>>the sp which lists the user defined sp

Select name from sysobjects where xtype='p'

Madhivanan

Failing to plan is Planning to fail

execute this in master database or

Select name from master..sysobjects where xtype='P'

----------------------------------
'KH'

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-02-02 : 06:54:55

quote:
Originally posted by iamsmahd

hi

How can get the list of system stored procedures in SQL server 2000?
I know that all the stored procedures which begin with sp_ are system sps. but what are the main stored procedure between them?
for Example, the sp which lists the user defined sp,...etc?

Thank you

sonia

BTW - sp_ is not necessarily a system stored procedure. You CAN create a user stored procuedre, in master, named sp_ ... it is generally considered bad practice, though there maybe time where you want/need to do this.

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-02 : 09:16:49

>>Select name from master..sysobjects where xtype='P'

If you run this in Default Databases, you will get all system sps listed. Instead use User defined Database