October 4, 2002 at 2:01 pm
Just curious what everyone's thoughts are on creating user-defined stored procedures in the Master database. Is this considered a bad practice or a good idea?
I'm considering adding some administrative-type stored procedures that I would be using on several different databases so creating them in the Master database makes the most sense. But, I've also been a little cautious about making changes to the Master database for obvious reasons. Anyone have any thoughts on the matter?
Thanks in advance.
Kevin Tanferani
October 4, 2002 at 3:20 pm
I avoid it. Easy to forget when you rebuild a server or install a new one. Try to set things up in a DBA database that I attach to all servers.
Steve Jones
October 4, 2002 at 6:43 pm
I do it but generally for things I don't want people to know are there which are for me maintainence items. I have done for a few sp's that are so reused they are commonly accessed in all databases in someway. But from a standpoint of pratice, it is bad, bad, bad, not that I always listen. If you do, then make sure you have this documented and have a backup copy on hand of the code. If it is just so you can use in every db and have availble to all new DBs consider, put a copy in each DB and a copy in the Model DB so it will be propigated to each new DB added. It will not hurt you but can bite you if you do not document it.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 5, 2002 at 5:28 am
I do not. In cases where I need the proc available in every db I just add to model.
Andy
October 7, 2002 at 10:01 am
I have a library database that I create all my systemwide stored procedures in. Since I haven't found a way to run the procedures directly from there (without the tedium of specifying that rather long db name also), and since I don't want to copy them to all 150+ dbs, I name them sp__xxxxx (two underscores, to differentiate them from normal system stored procedures) and COPY them to the master db. But I'd never want my ONLY copy to be in master.
October 16, 2002 at 1:17 pm
I keep an admin database on each server with all of the procs that we use. However, due to some of the procs using sysobjects of the master database, I put those in the master as well. I preface all of them with dba_ to make sure that they don't get mixed up in list.
October 17, 2002 at 8:29 am
I have never seen a reason not to put a stored procedure in the master database, it is after all just a stored procedure and if you have tested it completely and know what it does and need to use it in every database then I don't see the harm in placing it in the master database.
Also If you place it in multiple databases then whenever you have to make a change you must place the modified SP in 10, 20, or more databases.
Having said this, up until yesterday, I never placed an SP in the master database for daily use. Yesterday I placed a revised version of an SP I use to scrip tables in all my master databases with the intent that I would only have to place and maintain it in about 7 databases instead of 21 or more.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 17, 2002 at 8:52 am
All your responses against placing user-defined stored procedures in the master database got me curious as to whether Microsoft has said if it was a bad idea or not.
I searched in BOL for stored procedures and found an example under creating that seems to indicate that Microsoft doesn't care if you create what it calls a user-defined system stored procedure. Here is what I found in BOL:
H. Create a user-defined system stored procedure
This example creates a procedure to display all the tables and their corresponding indexes with a table name beginning with the string emp. If not specified, this procedure returns all tables (and indexes) with a table name beginning with sys.
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sp_showindexes' AND type = 'P')
DROP PROCEDURE sp_showindexes
GO
USE master
GO
CREATE PROCEDURE sp_showindexes
@@TABLE varchar(30) = 'sys%'
AS
SELECT o.name AS TABLE_NAME,
i.name AS INDEX_NAME,
indid AS INDEX_ID
FROM sysindexes i INNER JOIN sysobjects o
ON o.id = i.id
WHERE o.name LIKE @@TABLE
GO
USE pubs
EXEC sp_showindexes 'emp%'
GO
Of course you will have to keep track of these user-defined system stored procedures so that you don't loose them. I have stored the one I created in Source Safe.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
October 17, 2002 at 11:49 am
For stored procedures I intend to use as a system stored procedure I have placed in the master database. A good example is sp_blocker_pss80 which is included with Microsoft's knowledge base article on monitoring SQL Server blocking. If the stored proc starts with sp_, SQL Server is going to look in master for it first anyway.
But generally I do not include my own stored procedures in the master. I use a database specifically for my own tables and stored procedures in most cases. This is the habit I had gotten other DBAs to do as well when I was doing mentorship.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
October 17, 2002 at 6:28 pm
The only reason I have to have user sps in master is to be able to run them from any database with out the need to copy them to that database.
i.e use fredsdb
exec sp_dostuff
this executes sp_dostuff in the context of fredsdb
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply