Adding filters to SQL query (ORACLE database)

Question

I need to list tables, columns and their data type; I used this code:

select TABLE_NAME, COLUMN_NAME, DATA_TYPE 
from dba_tab_columns 
order by TABLE_NAME;

The problem is that the database is too large and I only need 2000 specific table/column/datatype. Knowing that a table with the same name and column can be present in multiple SCHEMA

For example (I have 2000 lines like that table names are very random and have nothing in common so is the case for the column names):

TABLE            Column           SCHEMA
------------------------------------------
DMT_AAAAAAA        C1111          ANT_A1
DMT_AAAAAAA        C1111          ANT_A2
BBBBBBBB           A4444          ANT_A3
JHD6365            H5525          ZUGRU
WRK679             C3020          MUSTSU
TDG5378            C66739         SHGUY

I tried to filter by schema using this query :

select TABLE_NAME, COLUMN_NAME, DATA_TYPE 
from dba_tab_columns 
where OWNER in ('ANT_A1', 'ANT_A2', 'ANT_A1', 'ZUGRU', 'MUSTSU', 'SHGUY') 
order by TABLE_NAME;

The issue now is that it lists all columns even the ones I don't need.

Is there a way to filter out only the lines needed?

Expected output :

|table name   |   column    |  data type|
DMT_AAAAAAA        C1111          NUMBER
DMT_AAAAAAA        C1111          VARCHAR
BBBBBBBB           A4444          NUMBER
JHD6365            H5525          VARCHAR
WRK679             C3020          VARCHAR
TDG5378            C66739         VARCHAR

Thank you.

Query and get distinct data that you need in to separate tables. Remove the unwanted data in those, Then join those with the main table — Srinika Pinnaduwage
– Srinika Pinnaduwage, Commented May 6, 2020 at 11:37
I don't understand your issue. You only want certain schemas, so you use owner in (...). Now you want only certain columns, too, so why don't you just add a condition column_name in (...)? — Thorsten Kettner
– Thorsten Kettner, Commented May 6, 2020 at 12:25
How can you expect this output? You select schemas ANT_A1 and ANT_A2, but your result contains tables from other schemas (ZUGRU, MUSTSU, ...). — Thorsten Kettner
– Thorsten Kettner, Commented May 6, 2020 at 12:37
@ThorstenKettner the column names are very random i gave a wrong example, i edited it the names are really random ( i Added few examples ) — TenEM
– TenEM, Commented May 6, 2020 at 12:39
@Srinika to be honest i didn't understand the idea you gave me i'm really new to oracle and sql like 2 days ago ^_^ — TenEM
– TenEM, Commented May 6, 2020 at 12:41

Thorsten Kettner · Accepted Answer · 2020-05-06 13:58:06Z

2

If you want to show the information about certain columns across schemas and tables, specify them in an IN clause using tuples:

select owner, table_name, column_name, data_type
from dba_tab_columns 
where (owner, table_name, column_name) in 
(
  ('DMT_AAAAAAA', 'C1111', 'ANT_A1'),
  ('DMT_AAAAAAA', 'C1111', 'ANT_A2'),
  ('BBBBBBBB', 'A4444', 'ANT_A3'),
  ('JHD6365', 'H5525', 'ZUGRU'),
  ('WRK679', 'C3020', 'MUSTSU'),
  ('TDG5378', 'C66739', 'SHGUY')
)
order by owner, table_name, column_name;

edited May 6, 2020 at 13:58

answered May 6, 2020 at 12:50

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

TenEM Over a year ago

returns this error: ERROR at line 4: ORA-00920: invalid relational operator

wolφi Over a year ago

Small typo, should be column_name not column, name. But thanks, Thorsten, you understood the question. I'll delete my answer, then, ok?

Thorsten Kettner Over a year ago

@wolφi: Thank you for pointing out my mistake. Stupid me, I could just have tried the query before posting it :-)

Thorsten Kettner Over a year ago

@Tarik MESSAOUDI: I've corrected the typo in my query.

wolφi Over a year ago

@ThorstenKettner I wouldn't call anybody stupid, and thanks for pointing out the IN ((a,b,c)) syntax!

|

Srinika Pinnaduwage · Accepted Answer · 2020-05-06 13:30:45Z

1

Check whether the following is enough. (I'm considering your output only)

select Distinct TABLE_NAME, COLUMN_NAME, DATA_TYPE from dba_tab_columns

If you don't want the tables, 'BBBBBBBB' and 'WRK679'

Put those in to a table (say tblExclude) and you can use the following

select Distinct TABLE_NAME, COLUMN_NAME, DATA_TYPE from dba_tab_columns a
Left Join tblExclude b on a.TABLE_NAME = b.TABLE_NAME
Where b.TABLE_NAME is null

edited May 6, 2020 at 13:30

answered May 6, 2020 at 13:13

Srinika Pinnaduwage

1,0421 gold badge7 silver badges14 bronze badges

5 Comments

TenEM Over a year ago

it shows up the output as needed yet the problem i have is filtering the tables/columns i need if not i will have the output of the complete database and it's too long

Srinika Pinnaduwage Over a year ago

So that is why my earlier suggestion comes in. Say you need to get all distinct tables, you can get those by select Distinct TABLE_NAME from dba_tab_columns. Execute that and get the results, remove unwanted and put to a new table. do the same for the col name if you want to do so. Then join those 2 tables with dba_tab_columns. Also let me know, whether your needed table/column list is longer than unwanted table/column list or not

wolφi Over a year ago

@Srinika No need for DISTINCT here, table_name, column_name, data_type is already distinct.

Srinika Pinnaduwage Over a year ago

wolφi, are you sure :)

wolφi Over a year ago

@Srinika, sorry, didn't see that you don't have owner in the column list...

Collectives™ on Stack Overflow

Adding filters to SQL query (ORACLE database)

2 Answers 2

6 Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related