Oracle SQL Developer多表查询object_no列及排查无该列表的SQL问题

我来帮你解决这个问题——你的原SQL语句之所以会重复显示表名，还混入了含OBJECT_NO列的表，是因为USER_TAB_COLUMNS这个视图里每一行对应表的一个列，而不是一个表一行。你写的WHERE NOT COLUMN_NAME LIKE '%OBJECT_NO%'会把所有列名不是OBJECT_NO的行都返回，所以只要某个表有不止一列（几乎所有表都是），就会多次出现这个表名；而且哪怕表本身有OBJECT_NO列，只要它还有其他列，这些其他列对应的行也会被筛选出来，导致这个表被混入结果里。

1. 查询包含OBJECT_NO列的表 #

要精准找出有这个列的表，用DISTINCT去重（避免极端情况下同一表存在多列同名的情况），同时用UPPER规避大小写匹配问题：

SELECT DISTINCT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE UPPER(COLUMN_NAME) = 'OBJECT_NO'
ORDER BY TABLE_NAME;

2. 查询不包含OBJECT_NO列的表 #

这里有两种常用且可靠的方法：

方法一：用MINUS集合操作（最直观） #

从当前用户的所有表中，减去包含OBJECT_NO列的表，剩下的就是没有该列的表：

SELECT TABLE_NAME
FROM USER_TABLES
MINUS
SELECT DISTINCT TABLE_NAME
FROM USER_TAB_COLUMNS
WHERE UPPER(COLUMN_NAME) = 'OBJECT_NO'
ORDER BY TABLE_NAME;

方法二：用分组+HAVING子句 #

通过分组统计每个表中OBJECT_NO列的数量，等于0就说明该表没有这个列：

SELECT TABLE_NAME
FROM USER_TAB_COLUMNS
GROUP BY TABLE_NAME
HAVING COUNT(CASE WHEN UPPER(COLUMN_NAME) = 'OBJECT_NO' THEN 1 END) = 0
ORDER BY TABLE_NAME;

3. 同时展示两种表的状态（可选） #

如果想一次性看到所有表是否包含该列，可以用UNION ALL合并结果，清晰标注状态：

SELECT TABLE_NAME, '包含OBJECT_NO列' AS 状态
FROM USER_TAB_COLUMNS
WHERE UPPER(COLUMN_NAME) = 'OBJECT_NO'
GROUP BY TABLE_NAME
UNION ALL
SELECT TABLE_NAME, '不包含OBJECT_NO列' AS 状态
FROM USER_TABLES
MINUS
SELECT TABLE_NAME, '包含OBJECT_NO列' AS 状态
FROM USER_TAB_COLUMNS
WHERE UPPER(COLUMN_NAME) = 'OBJECT_NO'
ORDER BY 状态, TABLE_NAME;

内容的提问来源于stack exchange，提问作者emma