Often I want to check some data in a table, and I know that the table is, for example, in the AP schema.
I’m not sure what the table is called, and, while I could load the schema browser in SQL Developer or TOAD, I find it a lot quicker using SQL to check.
SELECT table_name , owner , num_rows FROM all_tables WHERE owner = 'AP' AND table_name LIKE '%INVOICE%' AND num_rows > 0 ORDER BY num_rows DESC;
num_rows value is updated when Gather Schema Statistics runs
For example – I installed Oracle Database 11g Express Edition and it comes with a tonne of tables as you’d expect, plus some tables in the HR schema.
I ran this SQL:
SELECT owner, table_name, num_rows, last_analyzed FROM ALL_TABLES WHERE owner= 'HR';
LAST_ANALZED was some time in 2014.
I did a quick Gather Schema Stats:
begin DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'HR', OPTIONS => 'GATHER', ESTIMATE_PERCENT=>dbms_stats.auto_sample_size, DEGREE=>4); end; /
Then re-ran the SQL and it returned:
OWNER TABLE_NAME NUM_ROWS LAST_ANAL ------- ------------ ---------- --------- HR REGIONS 4 21-OCT-17 HR LOCATIONS 23 21-OCT-17 HR DEPARTMENTS 27 21-OCT-17 HR JOBS 19 21-OCT-17 HR EMPLOYEES 107 21-OCT-17 HR JOB_HISTORY 10 21-OCT-17 HR COUNTRIES 25 21-OCT-17
If I had inserted rows into some of the tables, and then re-ran the Gather Schema Stats, the num_rows would have been updated too.
Sometimes you might want to get to the bottom of the SQL that makes up a view. In Toad, you can do that by putting the cursor onto the view name in your SELECT statement, and then pressing F4, or, I think you can CTRL + Click the view name (that works in SQL Developer too). That opens up the view properties, and you can then click the “Script” tab to get at the details.
However, the time delay to get at that can sometimes be rather long. I get around it with this SQL:
SELECT view_name , TEXT FROM all_views WHERE view_name = 'PO_AGENTS_V';
The SQL making up the view is stored in the “TEXT” field.
Here’s an example – while working on a bit of SQL to find out about Projects in the pa_projects_all table, I can see there’s a column in the table called “project_status_code”. Somewhere is another table which holds the descriptive name of the various different project statuses.
As a quick example, you can dig out the details of any tables with a “project_status_code” column name, owned by “PA”.
-- COLUMNS SELECT atc.column_name , atc.data_type , att.table_name , att.owner , att.num_rows , att.last_analyzed FROM all_tab_columns atc JOIN all_tables att ON atc.table_name = att.table_name AND atc.owner = att.owner WHERE atc.owner = 'PA' AND column_name = 'PROJECT_STATUS_CODE';
You can also use the same SQL to look for specific column names in the same table – for example, what if you wanted to look for all of the columns in the “pa_events” table with “BILL” in their name? Try something like:
-- COLUMNS SELECT atc.column_name , atc.data_type , att.table_name , att.owner , att.num_rows , att.last_analyzed FROM all_tab_columns atc JOIN all_tables att ON atc.table_name = att.table_name AND atc.owner = att.owner WHERE att.table_name = 'PA_EVENTS' AND atc.owner = 'PA' AND column_name LIKE '%BILL%';
You can also use SQL to look for column names in Views.
-- COLUMNS FOR VIEWS SELECT atv.view_name , atc.column_name , atc.column_id , atc.data_type , atv.owner FROM all_tab_columns atc , all_views atv WHERE atc.table_name = atv.view_name AND atc.owner = atv.owner AND atv.view_name LIKE 'MTL%EAM%ASSET%V' -- AND column_name LIKE '%OWN%' -- AND atc.owner = 'CSI' ORDER BY 1, 3;
Other ALL% Tables
There are many many other ALL% tables you can make use of.
Here are some handy ones (courtesy of Oracle – Get list of all Tables, Views, Procedures, Functions, Triggers).
Get a list of Procedures
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='PROCEDURE'; --OR SELECT * FROM ALL_PROCEDURES WHERE OWNER='HR' AND OBJECT_TYPE='PROCEDURE';
Get a list of Functions
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='FUNCTION'; --OR SELECT * FROM ALL_PROCEDURES WHERE OWNER='HR' AND OBJECT_TYPE='FUNCTION';
Get a list of Triggers
SELECT * FROM USER_PROCEDURES WHERE OBJECT_TYPE='TRIGGER'; --OR SELECT * FROM ALL_TRIGGERS WHERE OWNER='HR'; --OR SELECT * FROM ALL_PROCEDURES WHERE OWNER='HR' AND OBJECT_TYPE='TRIGGER';
Get a list of Packages
SELECT * FROM ALL_OBJECTS WHERE object_type = 'PACKAGE';