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

liukuo165

于 2013-09-04 10:34:01 发布

阅读量152

点赞数

分类专栏：数据库文章标签： mysql

本文链接：https://blog.csdn.net/liukuo165/article/details/84476034

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

查看数据库为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')