title: How to create a Script that Archives SPs with variables in table name and DB name?
body: <p>So I know how to do this for all my tables:</p>
  
  <pre><code>DECLARE @month VARCHAR(9)
  DECLARE @db VARCHAR(10)
  DECLARE @sql NVARCHAR(max)
  
  SET @month = (SELECT Datename(month, Getdate()))
  SET @db = 'MyDatabase'
  SET @sql = 'SELECT * INTO ' + @db + '.dbo.MyTable_' + @month
             + ' FROM ' + @db + '.dbo.MyTable'
  
  PRINT @sql
  
  EXEC Sp_executesql @sql  
  </code></pre>
  
  <p>But I can't fit several large SPs into @sql variables and even if I could apparently:</p>
  
  <p><em>CREATE/ALTER PROCEDURE' does not allow specifying the database name as a prefix to the object name</em></p>
  
  <p>Is there a way to script an SP transfer without its entire text?</p>
  
  <p>If so, how can I get the database name in a variable for <code>USE [dbname]</code></p>
  
  <p>I know with <code>sqlmcd</code> mode I can do something like</p>
  
  <pre><code>:setvar dbname "MyDatabase" 
  USE $(dbname)
  </code></pre>
  
  <p>but I would need to have the dbname as a variable also like</p>
  
  <pre><code>DECLARE @db VARCHAR(10)
  SET @db = (SELECT Datename(year, Getdate()))
  :setvar dbname @db
  USE $(dbname)
  </code></pre>
  
  <p>So, the goal is to archive off a bunch of SPs each Month into a database, with new db for each year without having to ever manually change the script.</p>