SELECT

t.TABLE_NAME,

t.CONSTRAINT_TYPE,

c.COLUMN_NAME,

c.ORDINAL_POSITION

FROM

INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c

WHERE

t.TABLE_NAME = c.TABLE_NAME

AND t.TABLE_SCHEMA = "test"

AND t.CONSTRAINT_TYPE = "PRIMARY KEY";

這是表示查詢表有主鍵，改一下就知道那些表不含主鍵了。

獲取資料庫中除系統庫之外所有無主鍵的表sql如下：

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"

);