Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 simple code then that would best idea.

like image 907
MadBoy Avatar asked Jun 10 '11 08:06

MadBoy


People also ask

How can I find all foreign key references to a table in SQL Server?

Using SQL Server Management Studio Open the Table Designer for the table containing the foreign key you want to view, right-click in the Table Designer, and choose Relationships from the shortcut menu. In the Foreign Key Relationships dialog box, select the relationship with properties you want to view.

How do you reference a primary key in SQL?

SQL PRIMARY KEY on ALTER TABLE. ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created).

Which key is referencing a primary key?

A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.


2 Answers

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
like image 105
Paul Alan Taylor Avatar answered Oct 24 '22 05:10

Paul Alan Taylor


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'
like image 23
CloudyMarble Avatar answered Oct 24 '22 05:10

CloudyMarble