[Tip] find table that has the X ...

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

These days I had to figure out the name of a table. I just knew the name of 2 fields of this table. Then I did this SQL to figure it out.

The SQL below searches for the name of the tables that has the fields informed in the existing clause.

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:

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
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 13 Apr 2005 5:09 pm
Location: Volta Redonda - RJ
Grata,

Analista de Sistemas

+ Simple:

Select all

SELECT TABLE_NAME FROM USER_TAB_COLUMNS  
WHERE COLUMN_NAME = '<nome do meu campo';
[] 's
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

I did this, but it appeared soooo tables.
I had to filter using 2 fields! :-)

Type, all tables that have field X and Y ... Did you?
Analista
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 24
Joined: Wed, 13 Apr 2005 5:09 pm
Location: Volta Redonda - RJ
Grata,

Analista de Sistemas

Cool!
That's why I like this forum!

: D
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 5 guests