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 58 · Answer 1 · answered Aug 08 '12 at 14:00

58

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

23,518
5
56
58

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 Jun 08 '22 at 17:34

0

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';

edited Jun 08 '22 at 17:34

MarredCheese

17,541
8
92
91

answered Apr 19 '21 at 09:53

Vignesh Baskaran

53
2
8

1) I had to include the schema to avoid the "Invalid object name" error (`INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS`). 2) The `REFERENTIAL_CONSTRAINTS` table doesn't contain either of these columns: `REFERENCED_TABLE_NAME`, `TABLE_NAME`. See [here](https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/referential-constraints-transact-sql). – MarredCheese Jun 08 '22 at 17:32

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