Copying SPs Automatically to New DB

  • 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

  • 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

  • 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

  • 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.

    K. Brian Kelley

    bkelley@sqlservercentral.com

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

    K. Brian Kelley
    @kbriankelley

  • I want this for the local database. If I use if on the master database, I will have to do so same for the database on the web. The problem there is that we usually develop sites and host it on shared hosting servers where we do not have access to master database. So we better locally create it into individual databases and then upload the same to web database.

    Paras Shah

    Evision Technologies

    Mumbai, India


    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