Re: SQL Stored procedures - find all within a database

From: Pete Finnigan <pete_at_petefinnigan.com>
Date: Mon, 28 Jul 2003 22:01:26 +0100
Message-ID: <v7oXjBBm8YJ$EwLS@peterfinnigan.demon.co.uk>

Hi,

I would include 'PACKAGE' and 'PACKAGE BODY' if you are looking for all pl/sql and also you should check dba_objects not all_objects as DBA_OBJECTS includes all objects in the database, all_objects includes just those objects accessible by the user doing the query.

user_% views :- views that allow you to see objects you own. all_% views :- views that allow you to see objects you own and objects

                granted to you.

dba_% views :- views that allow you to see all objects in the database.

joining owner=user will not include all objects as requested by the poster either as it would be restricted to the user of the query.

hth

kind regards

Pete

In article <bg3ntm$11c$1_at_svr8.m-online.net>, Thomas Kellerer <spam_eater_at_gmx.net> writes
>Arj schrieb:
>
>> Can someone give me an sql statement to find all the stored procedures
>> within a database.
>>
>> Thanks,
>> Arj
>
>select owner, object_name, object_type
>from sys.all_objects
>where object_type in ('PROCEDURE', 'FUNCTION')
>and owner = user
>

-- 
Pete Finnigan
email:pete_at_petefinnigan.com
Web site: http://www.petefinnigan.com - Oracle security audit specialists
Book:Oracle security step-by-step Guide - see http://store.sans.org for details.

Received on Mon Jul 28 2003 - 16:01:26 CDT