Which system stored procedures do you use?

I realise this is subjective, but I figure some discussion on here can't be a bad thing!

The system stored procedures are pushed into the following categories in MSDN:

Active Directory Stored Procedures
Catalog Stored Procedures
Change Data Capture Stored Procedures
Cursor Stored Procedures
Database Engine Stored Procedures
Database Mail and SQL Mail Stored Procedures
Database Maintenance Plan Stored Procedures
Distributed Queries Stored Procedures
Full-Text Search Stored Procedures
Log Shipping Stored Procedures
Automation Stored Procedures
Policy-Based Management Stored Procedures
Replication Stored Procedures
Security Stored Procedures
SQL Server Profiler Stored Procedures
SQL Server Agent Stored Procedures
XML Stored Procedures
General Extended Stored Procedures

Which of those categories do you make use of?

asked Nov 23 '09 at 08:55 PM in Default

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

3 answers:

for DBA code I love sp_msforeachdb and sp_msforeachtable

answered Nov 24 '09 at 12:39 AM

nkav
101 ● 1 ● 1 ● 2

Ahh yes! Matt you missed off one important category : Undocumented! :)

Nov 25 '09 at 05:46 AM Kev Riley ♦♦

(comments are locked)

I guess I would probably use these every week.....

Database Engine Stored Procs

attach / detach
sp_helpXXXX
sp_who(2)

Database Mail

sending mail

Security Stored Procs

adding users/roles etc

SQL Server Profiler Stored Procs

server side tracing

SQL Server Agent Stored Procs

job creation and management

General Extended Stroed Procs

xp_cmdshell !!!

answered Nov 24 '09 at 06:15 AM

Kev Riley ♦♦
46.1k ● 38 ● 43 ● 69

I upvoted this, but I missed the xp_cmdshell reference with the exclamation points. I don't use it and have it disabled on my 2005/2008 servers. If I need to do something at the Command Line level I just think SQL Server isn't the right place for it.

Nov 25 '09 at 01:39 AM Jack Corbett

Jack: I agree about it being disabled and it is on my production boxes, but I do use it for admin tasks. In my scripts I test for the version, and if its 9 or above, turn on xp_cmdshell, do what I need to do (create folders or whatever), then turn it off again. It certainly isn't used by any application code! Me ONLY!

Nov 25 '09 at 04:56 AM Kev Riley ♦♦

(comments are locked)