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

0 comments:
Post a Comment