Schema Browser SQL

Table Information

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;

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

The 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.

Views

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.

Columns

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