mysql如何查看所有表的主键_mysql 查看所有没有主键的表

SELECT

#CONCAT("truncate table ",table_name,";")

table_name

FROM

information_schema. TABLES

WHERE

table_schema = 'test'

AND TABLE_NAME NOT IN (

SELECT

table_name

FROM

information_schema.table_constraints t

JOIN information_schema.key_column_usage k USING (

constraint_name,

table_schema,

table_name

)

WHERE

t.constraint_type = 'PRIMARY KEY'

AND t.table_schema = 'test'

)

将test改成自己mysql的数据库名，即可查出结果。这个sql的结果会包含view。

下面这个sql只会返回table。

USE INFORMATION_SCHEMA;

SELECT

TABLES.table_schema,TABLES.table_name

FROM TABLES

LEFT JOIN KEY_COLUMN_USAGE AS c

ON (

TABLES.TABLE_NAME = c.TABLE_NAME

AND c.CONSTRAINT_SCHEMA = TABLES.TABLE_SCHEMA

AND c.constraint_name = 'PRIMARY'

)

WHERE

tables.table_type <>"VIEW" and TABLES.table_schema in ('test1','test2','test3')

AND c.constraint_name IS NULL;