Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
on 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
-
on 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
on 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
-
on Tue, 21 Jun 2005 11:26 am
Show de bola!
That's why I like this forum!

-
Analista
- Location: Volta Redonda - RJ
Grata,
Analista de Sistemas
Return to SQL
Users browsing this forum: Google [Bot] and 3 guests