首页 > 其他 > 详细

How do I see all foreign keys to a table or column?

时间:2016-10-07 07:44:44      阅读:232      评论:0      收藏:0      [点我收藏+]

 

down voteaccepted

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = ‘<database>‘ AND
  REFERENCED_TABLE_NAME = ‘<table>‘;

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = ‘<database>‘ AND
  REFERENCED_COLUMN_NAME = ‘<column>‘;

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

 

use INFORMATION_SCHEMA;
select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE
where TABLE_SCHEMA = "<your_database_name>" and TABLE_NAME = "<your_table_name>" 
and referenced_column_name is not NULL;

How do I see all foreign keys to a table or column?

原文:http://www.cnblogs.com/kungfupanda/p/5935368.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!