Programmatically copy stored procedure to another db

Question

Post reply

Programmatically copy stored procedure to another db

stuthedog

Mr or Mrs. 500

Points: 513
More actions
March 7, 2012 at 2:28 pm

#252058

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

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

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!

stuthedog Mr or Mrs. 500 Points: 513 More actions · Answer 2

stuthedog

Mr or Mrs. 500

Points: 513

March 7, 2012 at 4:01 pm

#1456310

Beautiful! Thanks a bunch!

ztelmis1 Newbie Points: 2 More actions · Answer 3

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