Vamshi Vamshi - 2 months ago 11
SQL Question

In a Oracle Schema I want to know how many Tables, Procedures, Functions are there? How to find?

In a Oracle Schema I want to know how many Tables, Procedures, Functions are there? How to find?

Answer


You can use SQL developer which gives you a very good GUI interface for oracle database. But still you want it from database you can use below queries,

For tables,

SELECT OWNER, TABLE_NAME FROM ALL_TABLES; - Provides you details of all tables and user of that table
SELECT TABLE_NAME FROM USER_TABLES; - Provides you details of Current user. It dosen't have owner column

For Procedures, functions and pakages

SELECT * FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN('FUNCTION','PROCEDURE','PACKAGE')

If you want to find the count of rocedures, functions and pakages

SELECT OBJECT_TYPE, COUNT(*) FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN('FUNCTION','PROCEDURE','PACKAGE')
GROUP BY OBJECT_TYPEIN

SQL developer link http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html