2

I usually search column names in Oracle as we have 1000+ tables. Is there simpler way to search column names using regex.

For example Column name have CURRENCY or COUNTRY etc.

Mohammed Khalique
  • 25
  • 7
  • do you want to get tables that have the columns containing `currency`, `country` etc? – Vamsi Prabhala Apr 16 '16 at 12:14
  • hi. my answer assumed you wanted to use a regex to search the column names and currency/country was just an example. as I see you're fine with a simple `LIKE`, which is fine by me. – pid Apr 16 '16 at 20:40

4 Answers4

3

I would use the answer of this SO question and dump all data to a text file.

At that point I'd use any good text editor with regex search to search the text file and have an immediate overview of what is what.

This works very well as long as tables and columns do not change too often.

Community
  • 1
  • 1
pid
  • 11,472
  • 6
  • 34
  • 63
2

If you really want to use a regular expression:

select c.*
from all_tab_cols
where regexp_like(column_name, 'CURRENCY|COUNTRY');

However, I would be more inclined to write:

select c.*
from all_tab_cols
where column_name like '%CURRENCY%' or
      column_name like '%COUNTRY%';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

I think it duplicate.

But can find it using below query.

SELECT column_name, table_name FROM user_tab_columns WHERE column_name like '%CURRENCY%' OR column_name Like '%COUNTRY%';
Pratiyush Kumar Singh
  • 1,977
  • 3
  • 19
  • 39
0

I would try something like this:

SELECT table_name, column_name from user_tab_columns

WHERE REGEXP_LIKE(column_name, 'country|currency', 'i' );

i : Perform case-insensitive matching.

The AG
  • 672
  • 9
  • 18