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

user_1584830 0
Viewed 19454

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?

sql-server-2008 sql-server
2 Answers

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