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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 7, 2012 at 3:09 pm

  #1456285

  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

  March 7, 2012 at 4:01 pm

  #1456310

  Beautiful! Thanks a bunch!

• ztelmis1

  Newbie

  Points: 2

  More actions

  January 28, 2021 at 7:15 am

  #3838258

  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