TheDataGirl

Sometimes we are searching for a value in a database but we are unsure which database, or table, or column we can find this specific value.

For example, I would like to search for a value ‘WINNING’ and find out which columns contain this value (and obviously the table and schema as well) and the column might be STATUS with value ‘WINNING’ and under table CUSTOMER and schema ALL_DATA.

There is no straight away, but this script helps to generate queries to find which schemas, tables, and columns contain this specific value. Simply copy these queries, and erase the final ‘UNION ALL’ and run to get a list.

select 'select distinct '||
c.column_name||','''||t.table_schema||'.'||t.table_name||'.'||c.column_name||
     ''' from '||
     t.table_schema||'.'||
    t.table_name||' where '||
    c.column_name||' like ''%WINNING%'''||
    ' union all '
 from information_schema.tables t
 inner join information_schema.columns c
            on c.table_name = t.table_name
            and c.table_schema = t.table_schema
 where t.table_schema not in ('information_schema', 'pg_catalog')
       and t.table_type = 'BASE TABLE'
       and c.data_type = 'character varying'
 order by t.table_schema;