0 List Store procedure in DB2/Oracle/MSSQL

 

DB2

syscat.routines has all the sp information including its contents.

List all store procedure in the database

select routineschema, routinename,specificname from syscat.routines where routinetype ='P'

The text column has the content of the store procedure.

Oracle

PORCEDURES view list all the store procedures in the database.

SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='PROCEDURE'; 

SELECT * FROM DBA_PROCEDURES  WHERE OBJECT_TYPE='PROCEDURE';

SELECT * FROM ALL_PROCEDURES  WHERE OBJECT_TYPE='PROCEDURE';

SOURCE view has the content of the store procedure.

 

SELECT * FROM USER_SOURCE WHERE NAME = 'STORED_PROC'; 

SELECT * FROM DBA_SOURCE WHERE NAME = 'STORED_PROC';

SELECT * FROM ALL_SOURCE WHERE NAME = 'STORED_PROC'; 

MSSQL

sys.procedures in each database has all the sp information in this db. To get all SP in all dbs, we can use sp_MSForEachDB .

    EXEC sp_MSForEachDB 'SELECT name   FROM  sys.procedures' 

Sys.objects also has the similar information.

select name from sys.objects where type='P';

To get detail of the sp, we can use sp_helptext

-- sp_helptext SPNAME

sp_helptext FindItemsByDataSet