Sql Query for generate script of all stored procedure in Database

In this post will show you, how to generate script that lists all stored procedure in single file.

below is the query for that :

Method 1 :

DECLARE @MYSP AS TABLE(ID INT IDENTITY(1,1), SPNAME VARCHAR(255),SPDESC VARCHAR(MAX))
INSERT INTO @MYSP 
SELECT ROUTINE_NAME,ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE  ROUTINE_TYPE='PROCEDURE' order by ROUTINE_NAME

DECLARE @MIN INT
DECLARE @MAX INT
SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @MYSP

WHILE @MIN <= @MAX
BEGIN
    DECLARE @sp_desc varchar(max)
    SELECT @sp_desc=SPDESC FROM @MYSP WHERE ID=@MIN
    
    print @sp_desc
    
    SET @MIN=@MIN+1
END

you can get list of all stored procedures from Information_Schema.Routines tables. after that you can loop each record and print SP.


Method 2 :

 DECLARE @MYSP AS TABLE(ID INT IDENTITY(1,1), SPNAME VARCHAR(255))

 INSERT INTO @MYSP SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES
 WHERE  ROUTINE_TYPE='PROCEDURE' order by ROUTINE_NAME

 DECLARE @MIN INT
 DECLARE @MAX INT

 SELECT @MIN=MIN(ID),@MAX=MAX(ID) FROM @MYSP

 declare @text nvarchar(max)

 WHILE @MIN <= @MAX

 BEGIN

    DECLARE @sp_name varchar(255)
    SELECT @sp_name=SPNAME FROM @MYSP WHERE ID=@MIN
    EXEC SP_HELPTEXT @sp_name    

    SET @MIN=@MIN+1

 END