CODEWITHSUNDEEP
sqloracle-database
Mohammed Khalique
Mohammed Khalique

Reputation: 25

How to check all column names of Tables in oracle with regex?

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.

Upvotes: 2

Views: 1578

Answers (4)

The AG
The AG

Reputation: 690

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.

Upvotes: 0

Pratiyush Kumar Singh
Pratiyush Kumar Singh

Reputation: 1997

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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

Upvotes: 2

pid
pid

Reputation: 11607

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.

Upvotes: 3

Related Questions