如何查询没有主键的表

🪐 TiDB 技术问题
1

如何查询没有主键的表

2

还有就是，也没有主键，也没有唯一键的表

3

试一下，应该可以

SELECT * FROM INFORMATION_SCHEMA.TABLES t 
WHERE NOT EXISTS (
	SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
	WHERE tc.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
	AND tc.TABLE_NAME = t.TABLE_NAME
)
1 个赞
4

information_schema这个表里应该能查到

5 
select table_schema,table_name
  FROM INFORMATION_SCHEMA.TABLES b
  WHERE
  b.TABLE_SCHEMA not in ('METRICS_SCHEMA','mysql','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA','test') and table_type='BASE TABLE' and
  (table_schema, table_name) not in
  (select TABLE_SCHEMA,TABLE_NAME from
  (select TABLE_SCHEMA,TABLE_NAME,INDEX_NAME,GROUP_CONCAT(COLUMN_NAME) as c,group_concat(NULLABLE) as n
  from INFORMATION_SCHEMA.STATISTICS
  where NON_UNIQUE=0 and TABLE_SCHEMA not in ('METRICS_SCHEMA','mysql','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA','test')
  group by 1,2,3) as tmp
  where tmp.n not like '%YES%');
2 个赞