如何找到所有具有引用特定 table.column 的外键并具有这些外键值的表? [英] How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

问题描述

我有一个表，其主键在其他几个表中被引用为外键.例如:

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`)
)

现在，我不知道数据库中有多少表包含 X 的外键，如表 Y 和 Z.是否有我可以使用的 SQL 查询返回: 1. 包含表的列表外键到 X AND 2. 这些表中哪些实际上在外键中有值?

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?

推荐答案

试试这个查询:

您必须使用sysreferences 和sysobjects 表来获取信息

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'

这篇关于如何找到所有具有引用特定 table.column 的外键并具有这些外键值的表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！