在Oracle数据库中查询表是否拥有主键可通过视图USER_CONSTRAINTS并限定条件CONSTRAINT_TYPE='P'来查询。查询索引数据可通过视图USER_INDEXES来查询。查询表的数据可通过查询USER_TABLES来获取数据。如果要查询没有主键的表,没有索引的表则需要通过将这些表进行关联来获取。而当每次想要查询没有索引的数据时候,不想自己写,于是网络上搜索,但搜索到的数据都是一堆乱七八糟的。所以整合了这些SQL的查询,首发在微信公众号
该SQL用于查询Oracle当前用户下主键和索引的情况。查询结果如下图所示
TABLE_NAME:表示表名
KEY_NUMBER:表示主键数量,等于0表示没有主键
INDEX_NUMBER:表示索引数量,等于0表示没有索引
SQL文本如下:
WITH INDEX_DATA AS (
SELECT TABLE_NAME,COUNT(*) NUMS FROM USER_INDEXES GROUP BY TABLE_NAME
),CONST_DATA AS(
SELECT T.TABLE_NAME,COUNT(*) NUMS FROM USER_CONSTRAINTS T WHERE T.CONSTRAINT_TYPE='P' GROUP BY T.TABLE_NAME
),QUERY_RESULT AS(
SELECT /*+ NO_MERGE(T) NO_MERGE(T1) NO_MERGE(T2) USE_HASH(T T1) USE_HASH(T T2)*/
T.TABLE_NAME,
NVL(T1.NUMS,0) INDEX_NUMBER,
NVL(T2.NUMS,0) KEY_NUMBER
FROM USER_TABLES T
LEFT JOIN INDEX_DATA T1 ON T.TABLE_NAME = T1.TABLE_NAME
LEFT JOIN CONST_DATA T2 ON T.TABLE_NAME = T2.TABLE_NAME
)
SELECT * FROM QUERY_RESULT T WHERE KEY_NUMBER=0 AND INDEX_NUMBER =0
如果你的数据库有多个用户,你想查询所有用户的数据,而不仅仅是当前用户,这时你可以通过将上面SQL中的USER_INDEXES视图替换为DBA_INDEXES视图,将USER_CONSTRAINTS视图替换为DBA_CONSTRAINTS视图,将USER_TABLES视图替换为DBA_TABLES视图即可查询所有用户的数据,但前提是你得拥有查询数据字典的权限。