mysql查看一个数据库所有的主外键

查看数据库为CRM的所有表的主外键

SELECT * FROM information_schema.key_column_usage   WHERE table_schema='crm'

只查看外键

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE constraint_schema = 'crm'

SELECT * FROM information_schema.key_column_usage   WHERE table_schema='crm' AND constraint_name !='PRIMARY';

查看外键名,表名,列名,引用表名,引用的列名
SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME FROM information_schema.key_column_usage   WHERE table_schema='crm' AND constraint_name !='PRIMARY';

//拼接删除外键语句
SELECT
CONCAT(' ALTER TABLE ',a.CONSTRAINT_SCHEMA,'.',a.TABLE_NAME,'  DROP FOREIGN KEY  ',a.CONSTRAINT_NAME,';')
FROM
information_schema.TABLE_CONSTRAINTS a WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
AND CONSTRAINT_SCHEMA IN ('crm')