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?

Answer 1

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'