Check reference of Primary key

Dheeraj Sharma

is there any way to find out any reference of primary key value exist in Database.

Id	Country Name
1	India

now, how can i find that india is in use in state table before delete.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sreekanth Reddy7y ago
Try this :

SELECT
obj.name AS FK_NAME,
sch.name AS [schema_name],
tab1.name AS [table],
col1.name AS [column],
tab2.name AS [referenced_table],
col2.name AS [referenced_column]
FROM
sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
ON col2.column_id = referenced_column_id
AND col2.object_id = tab2.object_id;
0
Dheeraj Sharma7y ago
I am asking from whole database not only state table.
Country and State is just for example.
0
Sreekanth Reddy7y ago
declare @cnt int;
set @cnt = select count(*) from state where countryId = 1;
IF(@cnt < 1)
delete from country where CountryId=1
0