April 22, 2011 at 4:20 am
Suppose that we have lots of tables related to each other, with no cascading on delete.
Is there a way to find out which are the records not referenced by any foreign key (orphaned), not by a query joining on the related tables but via sysobjects, indexes and so on - somehow generic, issuing just the tablename?
April 22, 2011 at 5:39 am
If you have foreign keys in place, then you should not have any orphans. That's the whole idea of referential integrity.
If your foreign keys are not enforced, then you could try enforcing them, but you won't get all orphans, only the first one. To get all orphans, you have to run a query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2011 at 10:15 am
Horatiu Ripa (4/22/2011)
Suppose that we have lots of tables related to each other, with no cascading on delete.Is there a way to find out which are the records not referenced by any foreign key (orphaned), not by a query joining on the related tables but via sysobjects, indexes and so on - somehow generic, issuing just the tablename?
If you're asking whether SQL Server stores metadata about a row that designates it as an orphan with respect to a specific foreign key, the answer is no. You will need to query the tables to find that out that information.
The question is, what are you trying to do? If your foreign keys were added WITH NOCHECK and you're trying to clean up orphans now then your best bet is to identify all the keys, write the queries you need to find all the orphans (queries with LEFT JOINs) and then decide on a plan to optionally archive and then purge the orphan data.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply