How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

Question

I have a table whose primary key is referenced in several other tables as a foreign key. For example:

CREATE TABLE `X` (
  `X_id` int NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`X_id`)
)
CREATE TABLE `Y` (
  `Y_id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `X_id` int DEFAULT NULL,
  PRIMARY KEY  (`Y_id`),
  CONSTRAINT `Y_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
)
CREATE TABLE `Z` (
  `Z_id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  `X_id` int DEFAULT NULL,
  PRIMARY KEY  (`Z_id`),
  CONSTRAINT `Z_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
)

Now, I don't know how many tables there are in the database that contain foreign keys into X like tables Y and Z. Is there a SQL query that I can use to return: 1. A list of tables that have foreign keys into X AND 2. which of those tables actually have values in the foreign key?

score 57 · Answer 1 · answered Aug 08 '12 at 14:00

57

try this query:

You have to use sysreferences and sysobjects tables to get the information

Query below gives all the foriegn keys as well as parent tables with column names from the database

select cast(f.name as varchar(255)) as foreign_key_name
, cast(c.name as varchar(255)) as foreign_table
, cast(fc.name as varchar(255)) as foreign_column
, cast(p.name as varchar(255)) as parent_table
, cast(rc.name as varchar(255)) as parent_column
from  sysobjects f
inner join sysobjects c on f.parent_obj = c.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects p on r.rkeyid = p.id
inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
where f.type = 'F'

answered Aug 08 '12 at 14:00

Joe G Joseph

22,627
4
51
56

you can also add: `and f.name = '[foreign key name]'` if you only want to look at a specific foreign key – Musical Coder May 31 '17 at 19:39
Just a note. This doesn't seem to work with composite foreign keys (ie. foreign keys back to composite PKs). It will only list one of the FK columns it seems. However, still an excellent query and very useful. – stoneMaster Jul 11 '17 at 14:57
1

This is great but it doesn't tell which of those tables actually have values for those foreign keys. I'm looking for that too... – Kristopher Mar 20 '18 at 13:28

score 0 · Answer 2 · edited Apr 19 '21 at 14:20

If you are only concerned about table names then you can use the below query:

select * from  REFERENTIAL_CONSTRAINTS where CONSTRAINT_SCHEMA='schema_name' and REFERENCED_TABLE_NAME='table_name' order by TABLE_NAME

or else you can get the table names from the above query and then get the foreign key metadata of that particular table using the below query:

SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE` WHERE  CONSTRAINT_SCHEMA='schema_name' and `TABLE_NAME` = 'table_name';

How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

2 Answers2

Linked