tsql - SQL Server 2008 R2 - programmatically copy stored procedure from one database to another -

i developing app provide separate database each subscriber. when new database needed stored proc on master db fires. creates new db , default tables. far good. need copy on several stored procs master db newly created db. not want maintain scripts or use 3rd party tools, needs dynamic.

right grabbing sp contents sql_modules attempting exec against new db. problem dont' know how change database exec() fires against, default db when stored proc run master, need target. i've tried changing procedure declaration create procedure [mynewdb].[dbo].[awesomesp] sql complains

'create/alter procedure' not allow specifying database name prefix object name.

answer follows:

arghh! easier expected this blog post found on kodyaz.com. else.

here code copies sp's in master database target database, can copy sp's filtering query on procedure name.

• @sql defined nvarchar(max)
• @name target database

code:

declare c cursor     select definition    [residazemaster].[sys].[procedures] p    inner join [residazemaster].sys.sql_modules m on p.object_id = m.object_id  open c  fetch next c @sql  while @@fetch_status = 0  begin    set @sql = replace(@sql,'''','''''')    set @sql = 'use [' + @name + ']; exec(''' + @sql + ''')'     exec(@sql)     fetch next c @sql end               close c deallocate c 

what work executing dbname.dbo.sp_executesql n'create procedure ...'. so, build string that. replace set @sql = 'use... with:

set @sql = n'execute ' + quotename(@name) + n'.dbo.sp_executesql n''' + @sql + ''''