Select tables matching condition from all tables

November 16, 2022

I want to select for all tables with columns that contain the word deposit from a list of all tables when the owner is like bi. How can I implement this?

For example, I can select all tables and owner:

SELECT * FROM ALL_TABLES WHERE OWNER LIKE '%BI';

But I want to then select all tables from table_name and run a query to find those columns that are type character and contain the string deposit. This I cannot understand how to do.

UPDATE:
The following query worked:

SELECT * 
FROM ALL_TAB_COLUMNS 
WHERE OWNER LIKE '%BI' 
AND DATA_TYPE 
LIKE '%NUMBER' 
AND COLUMN_NAME 
LIKE '%DEPOSIT%';

>Solution :

Just change the data_type to varchar% (to catch varchar and varchar2):

SELECT DISTINCT TABLE_NAME
  FROM DBA_TAB_COLUMNS TBLS
 WHERE UPPER(TBLS.OWNER)  LIKE '%BI'
   AND UPPER(COLUMN_NAME) LIKE '%DEPOSIT%'
   AND UPPER(DATA_TYPE)   LIKE 'VARCHAR%';