Oracle: Query table names and column names

Use the queries to find database tables for an owner (namespace/schema), tables containing certain phrases, all columns in a table, or column containing a certain phrase.

<pre>
-- All tables for an owner (prefix before table name) 
SELECT owner, table_name FROM all_tables where owner = 'PO';

-- Find table names 
SELECT owner, table_name FROM all_tables where Table_Name like '%INVOICE%INTER%' order by Owner, Table_Name 
SELECT owner, table_name FROM all_tables where lower(Table_Name) like '%pos_shipping_addresses%' order by Owner, Table_Name 

-- Find column names (along with data type and length/size) 
SELECT table_name, column_name, data_type, data_length from dba_tab_columns where lower(column_name) like '%ship%' and upper(table_name) = 'PO_HEADERS_INTERFACE'
</pre>

Uncategorized  

Leave a Reply