x

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.)

USE MASTER 
GO 
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 '10 at 12:23 AM in Default

Håkan Winther gravatar image

Håkan Winther
15.5k 33 37 48

(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 '10 at 01:01 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

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 '10 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 '10 at 05:34 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
90.9k 19 21 74

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x341
x11
x2

asked: Aug 17 '10 at 12:23 AM

Seen: 2064 times

Last Updated: Aug 17 '10 at 12:23 AM