[dba-SQLServer] Where do you put generic stored procedures

jwcolby jwcolby at colbyconsulting.com
Wed Jun 4 11:49:51 CDT 2008


Arthur,

Are you in your new home now?  From previous emails it 
sounded like this was move week.

So what do you think of the "have your own 'company' 
database" into which you throw these things.  They would be 
all in one place now.

John W. Colby
www.ColbyConsulting.com


Arthur Fuller wrote:
> Like all other things in SQL Server, there are several approaches. One is to
> place them in Master, where they will always be found. This demands that you
> back up master every time you make a change. Far too people IME back up
> master, which is plain silly, but that seems to be how it often is in the
> wild.
> 
> The down side to this approach is that if you supply the database to a
> client or similar, you'll need a script to generate the sprocs on the client
> machine. That's a not a large task, but I do recommend that you name the
> sprocs in a way that makes them easy to identify. While I'm on the naming
> topic, it's a best practice never to name a sproc with the prefix "sp". That
> is for MicroSoft. You might name yours "gp_*", which would isolate them
> alphabetically so you can easily grab them all.
> 
> A second approach, which I like for rock-solid things and which avoids the
> problem above, is to place them in Model. Model is so named because it is
> the template from which all new databases are created. You'll still have to
> create a script to create them in existing databases, but all future ones
> will automatically contain them (and anything else you add to Model). I have
> even created a Model that is the template for your standard order-entry
> database, with the tables for Customer, Order, Order Details, Product, etc.
> already in there. I might have to modify a few columns, but most of the
> grunt work gets done automatically using this method.
> 
> Of course, this method has a down side, too. Should you update one of your
> sprocs, you'll need to revisit the other databases and alter the sprocs in
> them.
> 
> Choose your poison.
> 
> hth,
> Arthur
> 
> On Wed, Jun 4, 2008 at 1:05 PM, jwcolby <jwcolby at colbyconsulting.com> wrote:
> 
>> I have a half dozen databases, which I am creating standard
>> field names for, and then creating parameterized stored
>> procedures to allow me to do things like drop specific
>> indexes, rebuild those indexes, update a set of hash fields etc.
>>
>> Where would I put these stored procedures.  I understand
>> some people do not think putting them in the Master database
>> is a good idea, but they do not "belong" to any of the
>> specific databases either.
>>
>> Do you create your own database and place them in there?
>> Some other strategy?
>>
>> --
>> John W. Colby
>> www.ColbyConsulting.com
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
> 
> 



More information about the dba-SQLServer mailing list