How to find out what Stored Procedures exist in AS400 (IBMi Power System) and Retrieve SQL Source

Query Stored Procedures

Well you can always use the System i Navigator as shown on the right here to view this info, generate SQL and do much more. But for a quick check there is a VIEW, named PROCEDURES, exists in the system that can simply be queried using STRSQL. Select * from PROCEDURES If you want to look for procedures in a specific schema (library) then use filter SELECT * FROM PROCEDURES WHERE PROCSCHEMA = '$libName'

The actual TABLE behind all this is SYSROUTINE and you can choose to directly query that as well. Here is the SQL source for the PROCEDURES view.

CREATE VIEW PROCEDURES AS 
SELECT VARCHAR(ROUTINE_SCHEMA , 18) AS PROCSCHEMA , VARCHAR(ROUTINE_NAME , 18) AS PROCNAME , 
VARCHAR(ROUTINE_DEFINER , 18) AS DEFINER , VARCHAR('' , 18) AS PKGSCHEMA , 
VARCHAR(EXTERNAL_NAME , 254) AS PROC_LOCATION, 
TRANSLATE(SUBSTR (PARAMETER_STYLE, 1, 1),'G',' ') AS PARM_STYLE , 
SUBSTR(EXTERNAL_LANGUAGE, 1, 8) AS "LANGUAGE" , SUBSTR('U', 1, 1) AS STAYRESIDENT , 
VARCHAR('', 254) AS RUNOPTS , SUBSTR('Y', 1, 1) AS FENCED , 
VARCHAR(LONG_COMMENT , 254) AS REMARKS , MAX_DYNAMIC_RESULT_SETS AS RESULT_SETS 
FROM QSYS2.SYSROUTINE 
WHERE ROUTINE_TYPE = 'PROCEDURE' 
AND LENGTH(ROUTINE_NAME) < 19  

Retrieve Stored Procedures SQL source

Now if you want to retrieve the SQL source for any specific Stored Procedure that is also very easy. Create a new member in the source physical file. Just save that as empty and then use this SQL

CALL GENERATE_SQL('$procedure_name', 
'$object_lib', 
'PROCEDURE', 
'$source_file', 
'$source_lib', 
'$member_name ',
STATEMENT_FORMATTING_OPTION=>'0')