I have a table in SQL Server 2008 R2 with primary key called ID which then is used by multiple tables around the database as foreign key. How to find out by which tables it is used? I'm trying to delete that record but it's complaining that ID is in use.
我在SQL Server 2008 R2中有一个表，其中主键称为ID，然后由数据库周围的多个表用作外键。如何找出使用哪个表？我正在尝试删除该记录，但它正在抱怨ID正在使用中。
Or maybe there's an easy way to delete all referenced records from whole database just by giving database that ID? Right now I'm going for each table (that I know has that ID as foreign key) and deleting records that correspond to that particular ID but if there's better/simpler way to find it and delete all at once with simple code then that would best idea.
或者也许有一种简单的方法可以通过给出数据库ID来从整个数据库中删除所有引用的记录？现在我要为每个表（我知道该ID作为外键）并删除与该特定ID对应的记录但是如果有更好/更简单的方法来查找它并使用简单代码一次性删除那么那么最好的主意。
4 个解决方案
#1
16
MS SQL benefits from being able to describe itself. In particular, there is a series of views that begin with INFORMATION_SCHEMA.
MS SQL能够描述自己。特别是，有一系列以INFORMATION_SCHEMA开头的视图。
To get an idea of where your field is used, try :-
要了解您的领域的使用位置，请尝试： -
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
#2
5
select constraintColumns.* from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as constraintColumns
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as constraints on constraintColumns.CONSTRAINT_NAME = constraints.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as keys on constraints.UNIQUE_CONSTRAINT_NAME = keys.CONSTRAINT_NAME
where keys.TABLE_NAME = 'yourTableWithThePrimaryKeyColumn';
#3
3
Take a look at the tables sysobjects and sysforeignkeys, you can get all foreignkeys that reference your table.
看一下表sysobjects和sysforeignkeys，你可以得到所有引用你的表的外键。
Try this untested Statement:
试试这个未经测试的声明：
select a.name as ConstraintName, f.name as FromTable, t.name as ToTable
from sysobjects a, sysobjects f, sysobjects t, sysforeignkeys b
where a.id=b.constid and f.id=b.fkeyid and t.id=b.rkeyid and t.name= 'Yourtable'
#4
1
just make the relation cascade on delete
只需在删除时建立关系级联
#1
16
MS SQL benefits from being able to describe itself. In particular, there is a series of views that begin with INFORMATION_SCHEMA.
MS SQL能够描述自己。特别是，有一系列以INFORMATION_SCHEMA开头的视图。
To get an idea of where your field is used, try :-
要了解您的领域的使用位置，请尝试： -
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
#2
5
select constraintColumns.* from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as constraintColumns
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as constraints on constraintColumns.CONSTRAINT_NAME = constraints.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE as keys on constraints.UNIQUE_CONSTRAINT_NAME = keys.CONSTRAINT_NAME
where keys.TABLE_NAME = 'yourTableWithThePrimaryKeyColumn';
#3
3
Take a look at the tables sysobjects and sysforeignkeys, you can get all foreignkeys that reference your table.
看一下表sysobjects和sysforeignkeys，你可以得到所有引用你的表的外键。
Try this untested Statement:
试试这个未经测试的声明：
select a.name as ConstraintName, f.name as FromTable, t.name as ToTable
from sysobjects a, sysobjects f, sysobjects t, sysforeignkeys b
where a.id=b.constid and f.id=b.fkeyid and t.id=b.rkeyid and t.name= 'Yourtable'
#4
1
just make the relation cascade on delete
只需在删除时建立关系级联