Create system stored procedures?

One of the best practicies i follow is not adding objects in master, but lately I have been thinking about actually adding some system stored procedures. I have a handful of procedures that I use to optimize / analyze the server and I use them in a user defined database, but to be able to get information about objects (like index names) from a specific database I need to pass a parameter for the DBname and use dynamic SQL to get the expected results.

If I add the procedures in master and use the sp_ prefix, then it would be possible to use the procedure from any database without passing the database name. It would even be possible to get all information from all the databases at once. (as long as I use the undocumented procedure to mark the procedure as a system stored procedure.)

 EXEC sp_ms_marksystemobject 

I know the implications of adding objects in master:

  • The procedure may get incompatible in upgrades (but that is the case where ever i put the procedure)

  • I may need to add the procedure everytime the SQL server i patched or when master is rebuilt, but that's not a critical problem.

  • Security risks, but that can be handled in a couple of different way, (like EXECUTE AS) and the procedures are only used for "system maintenance" and optimizations.

  • Need to backup master database more frequent, but the procedures only returns a resultset and not updating any data (unless I add the script for defragmentation of indexes that keeps statistics of how often the indexes get fragmented, but that is not crucial data).

  • etc

Is it so evil to add them as system stored procedures? Would you or are you using the master database for "user defined system stored procedures"?

more ▼

asked Aug 17, 2010 at 12:23 AM in Default

avatar image

Håkan Winther
16.6k 37 46 58

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

For me it sounds like your needs and the benefits derived from the implementation out-weigh the penalties/risks/drawbacks and you are, most importantly, considering this before hitting Execute so I would say carry on. I have added a procedure here and there under similar circumstances and things have stayed stable and all is well on the server(s) concerned.

So long as you consider the code and allow for its existence in the future then I would say that it would be worth doing. Above all, document it so that anyone following you will not get caught out if you are not around when they do the upgrade ...

more ▼

answered Aug 17, 2010 at 01:01 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

Fully agreed with Fatherjack and Grant, but I will add that I have done precisely what you are describing with certain maintenance procedures I wrote and it worked quite well for me.

You should only do something like this after thinking through all the implications, but you clearly have.

Aug 17, 2010 at 10:39 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

As long as you're working under the full understanding that anything you place in master might (probably won't) be nuked by a service pack or hot fix, there's nothing wrong with putting universal types of queries there. In fact, it's a good practice. Just be sure you keep a copy of the code somewhere else, not just in the backup of the master db.

And, I agree with @Fatherjack, document the heck of it so others know what's up.

more ▼

answered Aug 17, 2010 at 05:34 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Aug 17, 2010 at 12:23 AM

Seen: 3371 times

Last Updated: Aug 17, 2010 at 12:23 AM

Copyright 2018 Redgate Software. Privacy Policy