通过已知字段怎么查数据库表？（Oracle，达梦通用）三大方法

从最简单的知道字段查数据库表开始

查看字段名为WORLDID所在的表：
select * from dba_tab_cols WHERE column_name = ‘WORLDID’
当然，公司的数据库不同的表里肯定存在很多重复的字段，怎么办？
存在即合理，安排他！

当知道两个以上的字段

查询语句1（容易理解）

select P.TABLE_NAME FROM
(select TABLE_NAME,COLUMN_NAME from dba_tab_cols WHERE column_name = ‘A’)AS P
JOIN
(select TABLE_NAME,COLUMN_NAME from dba_tab_cols WHERE column_name = ‘B’)AS M
ON P.TABLE_NAME = M.TABLE_NAME
JOIN
(select TABLE_NAME,COLUMN_NAME from dba_tab_cols WHERE column_name = ‘C’)AS S
ON S.TABLE_NAME = M.TABLE_NAME
…
注：ABC…为字段名

查询语句2（加强版）

SELECT M.TABLE_NAME
FROM
(select column_name from dba_tab_cols WHERE column_name in (‘PREFIX’ , ‘REPLACE_NAME’ ,‘SUFFIX’,‘UNIVERSE’) GROUP BY column_name) AS P
LEFT JOIN dba_tab_cols AS M
ON P.column_name = M.column_name
GROUP BY M.TABLE_NAME
HAVING COUNT(*) = 4;

注：in（）里有几个字段，count（*）就等于几
理解：保证n个字段都要在查到的表里出现

查询语句3（加强版plus）

select TABLE_NAME from dba_tab_cols where column_name in (‘PREFIX’ , ‘REPLACE_NAME’ ,‘SUFFIX’,‘UNIVERSE’) group by TABLE_NAME having count(*) = 4;

Over Over