Suppose I have table A and B. Table A has a FK to Table B.
I could categorize records in B as 'referenced' and 'unreferenced', meaning that there's some record in the database that has a FK pointing to that record or not, respectively.
I want to find a scheme-independent way to delete all rows in B which are unreferenced. Doing a simple DELETE of table B without any constraints (because I want to be scheme-independent, i.e. a general way), almost certainly, it will fail.
If it could also be vendor independent would be great, but I feel I'm asking for too much.
Edit: Maybe I wasn't clear. When I say I don't want to depend on the scheme, I refer avoiding naming column names from other tables when doing the 'DELETE'. Just deleting records of B which can be deleted without breaking DB consistency.
The slow way:
declare del_cur cursor
local
forward_only
static
scroll_locks
for
select 0 from B
;
declare @foo int;
open del_cur;
fetch next from del_cur into @foo;
while @@fetch_status = 0
begin
begin try
delete from B where current of del_cur;
end try
begin catch
-- It was referenced, skip
end catch;
fetch next from del_cur into @foo;
end;
close del_cur;
deallocate del_cur;
A faster way would be examining the existing foreign keys and building a dynamic SQL statement that includes their column names.