Copying SPs Automatically to New DB

Question

Copying SPs Automatically to New DB

paras_98

SSCrazy

Points: 2994
More actions
April 27, 2002 at 7:05 am

#78307

What I want is, that whenever I create a database on the server, some of my procedures should automatically be copied from master database to new database. I got no clue from where to start, can anyone guide me thru?
Thankx for the help!
Paras Shah
Evision Technologies
Mumbai, India
Edited by - paras_98 on 04/27/2002 08:47:18 AM

Paras Shah
Evision Technologies
Mumbai, India

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

The model database is what is copied whenever a new database is created. If you are sure you want a certain set of objects in every user database, put them in model. That's what it is intended for. BTW, tempdb is created from the model database every time the server is stopped and restarted, so keep that in mind.

Since model is used for every database created, the problem with this choice is that every database created from that point forward will have those objects (including tempdb, as previously mentioned). As another method, you could script the objects to be created, then create your database and run the script against it. This would ensure your objects were built, and you wouldn't have to use the model database.

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

paras_98 SSCrazy Points: 2994 More actions · Answer 2

Thankx for the help. It works fine. There are some utility stored procedures that I have created which I intend to use it in every database so to be on a safe side to have them everytime, I want them to be created automatically.

quote:
The model database is what is copied whenever a new database is created. If you are sure you want a certain set of objects in every user database, put them in model. That's what it is intended for. BTW, tempdb is created from the model database every time the server is stopped and restarted, so keep that in mind.
Since model is used for every database created, the problem with this choice is that every database created from that point forward will have those objects (including tempdb, as previously mentioned). As another method, you could script the objects to be created, then create your database and run the script against it. This would ensure your objects were built, and you wouldn't have to use the model database.
K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

Paras Shah

Evision Technologies

Mumbai, India

Paras Shah
Evision Technologies
Mumbai, India

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

If these are utility stored procedures intended to be used in every database, you might want to try putting one in the master database and naming it with the prefix sp_ so that you only have to create one stored procedure in a single database.

System stored procedures preceded with sp_ were designed to be placed in master. Hence, if we use a stored procedure named sp_ in a user database, we always run into the SP:CacheMiss event if we do a Profiler trace.

An example utility stored procedure:



CREATE PROC sp_ShowUserTables

AS

SELECT '[' + USER_NAME(uid) + '].[' 

       + name + ']' TableName

FROM sysobjects

WHERE type = 'U'

If I place this in master, I can run it from anywhere just as EXEC sp_ShowUserTables and it'll return the recordset showing owner.tablename for me with respect to the database I'm currently using.