title: Copying a stored procedure from one database to another
body: <p>I have a central management database which collates some information and runs some dynamic SQL for various other tasks when a new database is restored into the environment. One of those tasks is going to be a bit complex to achieve through dynamic SQL so I had the idea of creating a master copy stored procedure in the central DB and copying that over to the new databases after they are restored.</p>
  
  <p>I've seen a few examples of people trying to do that on here but I can't get anything to play ball.</p>
  
  <p>Here's what i am trying to achieve conceptually, note that I'm trying to cater for potentially multiple stored procedures to be created in this way just for future proofing.</p>
  
  <pre><code>declare @sql nvarchar(max), @DatabaseName nvarchar(200)
  set @DatabaseName = 'TargetDatabase'
  
  set @sql =  
   (
     SELECT definition + char(13) + 'GO' 
     FROM sys.sql_modules s 
     INNER JOIN sys.procedures p 
     ON [s].[object_id] = [p].[object_id] WHERE p.name LIKE '%mastercopy%'
  )
  
  exec @sql
  </code></pre>
  
  <p>Thanks</p>