Determine if a row for an fk exists in any one of 20 tables

Question

I was trying to determine a better way than so many IF/THEN statements to determine if ONE OR MORE tables contain a ref to a forign key. I simply need to know (true/false) if a row exists in any one of 20 tables. All tables have the same fk field.

I hope this makes sense, if not i'll try to explain further.

Answer 1

Assuming that the 20 tables remains consistant then what about using a left outer join to each of the tables. probably inefficient way of doing it but should work

select Id from 
    (Select PK.id, isnull(tbl1.fk,0) as fk1, isnull(tbl2.fk,0) as fk2 ... etc
    from pk left join tbl1 on pk.id = tbl1.fk left join
     tbl2 on pk.id = tbl2.fk ... etc) as VirtualTable
Where fk1>0 or fk2>0 ... etc

Answer 2

So you're trying to work out if you can remove a particular row?

So... perhaps try:

begin tran;
delete tablename
where id = 3;
rollback tran;

Then see what error you get (if any), and how many rows are affected.

Answer 3

I'm not sure I understand the question. Would the following improve your situation?

if exists(select * from Table1 where ForeignKeyColumn = searchValue)
  or exists(select * from Table2 where ForeignKeyColumn = searchValue)
  or exists(select * from Table3 where ForeignKeyColumn = searchValue)
  or ...

Answer 4

What about something like:

if exists (
 select * from Table1 where MyKey = @key
 union
 select * from Table2 where MyKey = @key
 union
 select * from Table3 where MyKey = @key
 ...
)

I don't have a SQL server instance open in front of me so I'm sure that's got a syntax error somewhere, but you get the idea =)