首页 > 代码库 > How do I see all foreign keys to a table or column?

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

2024-08-15 20:43:44   210人阅读

 

down voteaccepted

For a Table:

SELECT   TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAMEFROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE  REFERENCED_TABLE_SCHEMA = ‘<database>‘ AND  REFERENCED_TABLE_NAME = ‘<table>‘;

For a Column:

SELECT   TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAMEFROM  INFORMATION_SCHEMA.KEY_COLUMN_USAGEWHERE  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_USAGEwhere 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?


联系
我们