Arthur Fuller
fuller.artful at gmail.com
Wed Jun 4 11:26:52 CDT 2008
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 > >