How to find out where reference to primary key is used in SQL Server?

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.

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 sim开发者_C百科ple code then that would best idea.

MS SQL benefits from being able to describe itself. In particular, there is a series of views that begin with INFORMATION_SCHEMA.

To get an idea of where your field is used, try :-

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

Take a look at the tables sysobjects and sysforeignkeys, you can get all foreignkeys that reference your table.

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'

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

Make the relation cascade on delete if the main table has an ownership relationship with other tables, meaning that by deleting the main row, other dependent rows in other tables should be deleted.

That's an option that might apply to your scenario.

The REFERENCES clauses of the CREATE TABLE and ALTER TABLE statements support the ON DELETE and ON UPDATE clauses. Cascading actions can also be defined by using the Foreign Key Relationships dialog box:

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]

More at http://msdn.microsoft.com/en-us/library/ms186973%28v=SQL.105%29.aspx