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.)
I know the implications of adding objects in master:
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"?
asked Aug 17, 2010 at 12:23 AM in Default
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 ...
answered Aug 17, 2010 at 01:01 AM
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.
answered Aug 17, 2010 at 05:34 AM
Grant Fritchey ♦♦