Programmatically copy stored procedure to another db

  • I am developing an app that will provide a separate database for each subscriber. When a new database is needed a stored proc on the master db fires. It creates a new db and default tables. So far so good. Now I need to copy over several stored procs from the master db to the newly created db. I do not want to maintain scripts or use 3rd party tools, it needs to be dynamic.

    Right now I am grabbing the SP contents from sql_modules then attempting to exec it against the new db. Problem is I dont' know how to change the database that exec() fires against, the default db when this stored proc is run is the Master, I need it to be the target. I've tried changing the procedure declaration to CREATE PROCEDURE [MyNewDb].[dbo].[AwesomeSP] but sql complains

    'CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name.

    Any suggestions?

    MS SQL 2008R2

  • stick with dynamic sql;

    something like this seems to work for me:

    declare

    @isql varchar(max),

    @dbname varchar(64)

    SET @dbname = 'TargetDatabase'

    declare c1 cursor for

    SELECT modz.definition

    FROM sys.sql_modules modz

    LEFT OUTER JOIN sys.objects objz

    WHERE type_desc IN ('SQL_SCALAR_FUNCTION',

    'CLR_SCALAR_FUNCTION',

    'CLR_TABLE_VALUED_FUNCTION',

    'SQL_INLINE_TABLE_VALUED_FUNCTION',

    'SQL_STORED_PROCEDURE',

    'CLR_STORED_PROCEDURE',

    'RULE',

    'SQL_TABLE_VALUED_FUNCTION',

    'USER_TABLE',

    'VIEW')

    open c1

    fetch next from c1 into @isql

    While @@fetch_status <> -1

    begin

    select @isql = 'USE ' + @dbname + ';' + @isql

    print @isql

    exec(@isql)

    fetch next from c1 into @isql

    end

    close c1

    deallocate c1

    GO

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Beautiful! Thanks a bunch!

  • hi

    this is correct.

    DECLARE @isql VARCHAR(MAX),

    @dbname VARCHAR(64)

    SET @dbname = 'TargetDatabase'

    DECLARE c1 CURSOR FOR

    SELECT modz.definition

    FROM sys.sql_modules modz

    LEFT OUTER JOIN sys.objects objz

    ON objz.object_id = modz.object_id

    WHERE type_desc IN ( 'SQL_SCALAR_FUNCTION', 'CLR_SCALAR_FUNCTION', 'CLR_TABLE_VALUED_FUNCTION',

    'SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_STORED_PROCEDURE', 'CLR_STORED_PROCEDURE', 'RULE',

    'SQL_TABLE_VALUED_FUNCTION', 'USER_TABLE', 'VIEW'

    )

    OPEN c1

    FETCH NEXT FROM c1

    INTO @isql

    WHILE @@fetch_status <> -1

    BEGIN

    SELECT @isql = 'USE ' + @dbname + ';' + @isql

    PRINT @isql

    EXEC (@isql)

    FETCH NEXT FROM c1

    INTO @isql

    END

    CLOSE c1

    DEALLOCATE c1

    GO

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

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