[Hint] Find table that has fields x ...

[dr_gori]

These days I had to find out the name of a table. I only knew the name of 2 fields of this table. Hence, did this SQL to figure that out.

The sql below search the names of tables that have the fields informed under clause Exists.

Code: Select all

SELECT A.TABLE_NAME
FROM USER_TABLES A
WHERE EXISTS (SELECT 1 FROM USER_TAB_COLUMNS
              WHERE TABLE_NAME=A.TABLE_NAME AND COLUMN_NAME = 'seu_campo'
             )
  AND EXISTS (SELECT 1 FROM USER_TAB_COLUMNS
              WHERE TABLE_NAME=A.TABLE_NAME AND COLUMN_NAME = 'seu_campo'
             )         

Example:

Code: Select all

SQL> SELECT A.TABLE_NAME
  2  FROM USER_TABLES A
  3  WHERE EXISTS (SELECT 1 FROM USER_TAB_COLUMNS
  4                WHERE TABLE_NAME=A.TABLE_NAME AND COLUMN_NAME = 'PC_TAXA'
  5               )
  6    AND EXISTS (SELECT 1 FROM USER_TAB_COLUMNS
  7                WHERE TABLE_NAME=A.TABLE_NAME AND COLUMN_NAME = 'FL_TIPO_TAXA'
  8               )         
  9    AND EXISTS (SELECT 1 FROM USER_TAB_COLUMNS
10               WHERE TABLE_NAME=A.TABLE_NAME AND COLUMN_NAME LIKE '%UF%'
11               )     
12  /

TABLE_NAME
------------------------------
TB_TARIFA_RCTRC
TB_TARIFA_RR
TB_TAXA_ESPECIAL

3 rows selected.

SQL>

[Analista]

+ simple:

Code: Select all

SELECT TABLE_NAME FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME = '<nome do meu campo';

[] 's

[dr_gori]

I did it, but appeared muuuuitas tables.
I had to filter using 2 fields!

Type, all tables that have the X and the Y ... get it?

[Analista]

Show de bola!
That's why I like this forum!