查询用了同一个字段名的表名？

[youlitwo]

如：A表有字段名：G，A1，A2，A3
B表有字段名：G，B1，A2，A3
C表中有字字段名：C1，C2，C3
.............
请问系统有没有表或视图可以找出哪些表用了G名做字段名？

[welyngj]

select table_name ,column_name from user_tab_columns where column_Name='G';

• 打赏
• 举报

回复 赞

[zgh2003]

select owner,table_name,column_name
from Dba_Col_Comments
where owner='USER_NAME'
and column_name like 'G%'

• 打赏
• 举报

回复 赞

[youlitwo]

如果是视图了

• 打赏
• 举报

回复 赞

[zgh2003]

SQL> select * from a;

A_COL_1 A_COL_2
---------- ----------
a 9
b 6
a 2
c 17
a 7
b 20
c 12
a 14
c 1
c 0
c 50

11 rows selected

SQL> create or replace view v_a as
2 select * from a where a_col_1='a'
3 ;

View created

QL> select * from v_a;

A_COL_1 A_COL_2
---------- ----------
a 9
a 2
a 7
a 14

SQL> conn sys/password as sysdba
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as SYS

SQL> select owner,table_name,column_name
2 from dba_col_comments
3 where owner='ZGH' and table_name='V_A'
4 ;

OWNER TABLE_NAME COLUMN_NAME
------- ----------- ----------------
ZGH V_A A_COL_1
ZGH V_A A_COL_2

结果说明：在视图dba_col_comments中，列TABLE_NAME可以是基础表名，也可以是视图名。

• 打赏
• 举报

回复 赞