Scripts all Sprocs at once with DROP and CREATE

Question

SQL Server 2008

Goal: To generate scripts for creating sprocs that are already in the DB. Must be one script per file.

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.

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'.

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 dbo.sp_executesql command because you cannot nest a CREATE PROCEDURE inside of an IF block

Tasks>Generate produces this:

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

But I require this (as found from right clicking the sproc):

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

Any ideas?

Answer 1

In order to create the scripts from SSMS...

Step 1 - create the scripting stored procedure:

IF EXISTS (
  SELECT * 
  FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[usp_ScriptProcedure]') 
  AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ScriptProcedure]
GO

CREATE PROCEDURE [dbo].[usp_ScriptProcedure] (
  @ObjectID INT,
  @Name NVARCHAR(128),
  @SchemaID INT
) 
AS 

DECLARE 
  @code VARCHAR(MAX),
  @newLine CHAR(2)

SET @newLine = CHAR(13) + CHAR(10)

SET @code = 
    'USE [' + DB_NAME() + ']' + @newLine + 'GO' + @newLine + @newLine
    + 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = '
    + 'OBJECT_ID(N''[' + SCHEMA_NAME(@schemaID) + '].[' + @Name + ']'') ' 
    + 'AND type IN (N''P'', N''PC''))' + @newLine 
    + 'DROP PROCEDURE [' + SCHEMA_NAME(@schemaID) + '].[' + @name + ']' 
    + @newLine + @newLine + 'SET ANSI_NULLS ON' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER ON' + @newLine + 'GO'
    + @newLine + @newLine
    + OBJECT_DEFINITION(@ObjectID) + @newLine + 'GO' 
    + @newLine + @newLine + 'SET ANSI_NULLS OFF' + @newLine + 'GO' 
    + @newLine + @newLine + 'SET QUOTED_IDENTIFIER OFF' + @newLine + 'GO'

WHILE @code <> ''
BEGIN
  PRINT LEFT(@code,8000)
  SET @code = SUBSTRING(@code, 8001, LEN(@code))
END
GO

Step 2 - enable xp_cmdshell

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Step 3 - run the script below (be sure to set server name and folder destination)

DECLARE 
  @name sysname,
  @objID int,
  @schemaID int,
  @cmd varchar(1000),
  @folder varchar(128),
  @server varchar(128)

SET @server = 'MSSQL'  
SET @folder = 'C:\Scripts'

DECLARE procs CURSOR FOR 
SELECT name, object_id, schema_id 
FROM sys.procedures
WHERE is_ms_shipped = 0 
ORDER BY [name]
OPEN procs

FETCH NEXT FROM procs
INTO @name, @objID, @schemaID

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @cmd = 'sqlcmd -S .\' + @server + ' -d ' + DB_NAME() 
           + ' -Q "EXEC usp_ScriptProcedure ' 
           + CONVERT(VARCHAR(20), @objID) + ', N'''
           + @name + ''', ' + CONVERT(VARCHAR(20), @schemaID) 
           + '" > ' + @folder + '\' + @name + '.sql'

  EXEC xp_cmdshell @cmd

  FETCH NEXT FROM procs
  INTO @name, @objID, @schemaID

END

CLOSE procs
DEALLOCATE procs

References:

• How to script all stored procedures in a database
• sys.procedures
• xp_cmdshell