how to check if a key of a record is used in other tables as foreign key (sql)?
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.idWHERE referenced_object_id = OBJECT_ID(N'[dbo].[TableName]')
You need to join all other tables. Like this:
select *from Parentswhere 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.