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

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 21 Jun 2005 11:00 am

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>
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 21 Jun 2005 11:11 am

+ simple:
Code: Select all
SELECT TABLE_NAME FROM USER_TAB_COLUMNS
WHERE COLUMN_NAME = '<nome do meu campo';
[] 's
Analista
Location: Volta Redonda - RJ

Grata,

Analista de Sistemas

Poston Tue, 21 Jun 2005 11:16 am

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?
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Tue, 21 Jun 2005 11:26 am

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

:D
Analista
Location: Volta Redonda - RJ

Grata,

Analista de Sistemas


  • See also
    Replies
    Views
    Last Post


Return to SQL

Who is online

Users browsing this forum: Google [Bot] and 3 guests