how to check if a key of a record is used in other tables as foreign key (sql)?

Question

I have a table that its primary key "ID" field is used in many other table as foreign key.

How can I realize that a record from this table (for example first record "ID = 1") is used in other table?

I don't want to select from all other tables to understand it cause tables are so many and relations either. I searched for a solution, there were no working solutions or I got it wrong. Please help.

Answer 1

For a Generic way use this and you will get all the tables that have the foreign key, and then u can make a loop to check all tables in list. This way u can add foreign keys and no change in code will be needed...

SELECT 
sys.sysobjects.name,
sys.foreign_keys.*
FROM 
sys.foreign_keys 
inner join sys.sysobjects on
    sys.foreign_keys.parent_object_id = sys.sysobjects.id
WHERE 
referenced_object_id = OBJECT_ID(N'[dbo].[TableName]') 

Answer 2

You need to join all other tables. Like this:

select *
from Parents
where
 exists(select * from Children1 where ...)
 or exists(select * from Children2 where ...)
 or exists(select * from Children3 where ...)

If all your FK columns are indexed this will be extremely efficient. You will get nice merge joins.