找出没有主键或唯一性约束的表

MySQL

SELECT
	t.table_schema,
	t.table_name,
ENGINE 
FROM
	information_schema.TABLES t
	INNER JOIN information_schema.COLUMNS c ON t.table_schema = c.table_schema 
	AND t.table_name = c.table_name 
GROUP BY
	t.table_schema,
	t.table_name 
HAVING
	sum( IF ( column_key IN ( 'PRI', 'UNI' ), 1, 0 ) ) = 0;

 

PostgreSQL

select
	tab.table_schema,
	tab.table_name
from
	information_schema.tables tab
left join information_schema.table_constraints tco on
	tab.table_schema = tco.table_schema
	and tab.table_name = tco.table_name
	and tco.constraint_type in ('PRIMARY KEY', 'UNIQUE')
where
	tab.table_type = 'BASE TABLE'
	and tab.table_schema not in ('pg_catalog', 'information_schema')
	and tco.constraint_name is null;

 

Oracle

select t.owner as schema_name,
       t.table_name
from sys.dba_tables t
left join sys.dba_constraints c
          on t.owner = c.owner
          and t.table_name = c.table_name
          and c.constraint_type = 'P'
where c.constraint_type is null
order by t.owner,
         t.table_name;