title: Scripts all Sprocs at once with DROP and CREATE
body: <p>SQL Server 2008</p>
  
  <p><strong>Goal:</strong> To generate scripts for creating sprocs that are already in the DB. Must be one script per file.</p>
  
  <p>I know I can simply right click the database and 'Tasks>Generate Scripts', but that doesn't script the sproc in the template I want.</p>
  
  <p>I need the sproc to be scripted in the same template you get when you right click the sproc from the object explorer and 'Script Stored Procedure As>DROP and CREATE'.</p>
  
  <p>It's true you get a similar version of this through 'Tasks>Generate Scripts' but the main difference is the 'Tasks>Generate Scripts' method creates the script via the <code>dbo.sp_executesql</code> command because you cannot nest a <code>CREATE PROCEDURE</code> inside of an <code>IF</code> block</p>
  
  <p>Tasks>Generate produces this:</p>
  
  <pre><code>USE someDB
  GO
  
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'somesproc') AND type in (N'P', N'PC'))
  DROP PROCEDURE someSproc
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'someSproc') AND type in (N'P', N'PC'))
  BEGIN
  EXEC dbo.sp_executesql @statement = N'-- =============================================
  CREATE PROCEDURE 
  AS
  BEGIN
  
  END
  ' 
  END
  GO
  </code></pre>
  
  <p>But I require this (as found from right clicking the sproc):</p>
  
  <pre><code>USE someDB
  GO
  
  IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'someSproc') AND type in (N'P', N'PC'))
  DROP PROCEDURE someSproc
  GO
  
  USE someDB
  GO
  
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE PROCEDURE someSproc
  
  AS
  BEGIN
      SET NOCOUNT ON;
  
  END
  
  GO
  </code></pre>
  
  <p>Any ideas?</p>