mysql查询无主键的表的方法sql代码
mysql 查找所有数据库中没有主键的表：select table_schema,table_name from information_schema.tables
where (table_schema,table_name) not in(
select distinct table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI'
)
and table_schema not in (
'sys','mysql','information_schema','performance_schema' --排除系统库
);
查询无主键的表：
SELECT
table_schema,
table_name
FROM
information_schema.TABLES
WHERE
table_name NOT IN ( SELECT DISTINCT table_name FROM information_schema.COLUMNS WHERE column_key = "PRI" )
AND table_schema IN ( 'caoss' );
查询无主键的表：
SELECT
table_schema,
table_name
FROM
information_schema.TABLES
WHERE
( table_schema, table_name ) NOT IN ( SELECT DISTINCT table_schema, table_name FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI' )
AND table_schema NOT IN ( 'sys', 'mysql', 'information_schema', 'performance_schema' );
