Monday, June 24, 2013

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'

image

The text column has the content of the store procedure.

image

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

image

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

image

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' 

image

Sys.objects also has the similar information.

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

image

To get detail of the sp, we can use sp_helptext

-- sp_helptext SPNAME
sp_helptext FindItemsByDataSet
image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates