How can I get all orphan records in a single query?

Question

I just noticed few tables that has orphan records (foreign keys) that no longer point to any primary key. How can I query the tables to get a list of all such rows? When these parent records got deleted or how to handle this?

I'm using SQL Server 2008 R2 and we are trying to import 3 similar databases into one after cleaning it up, please advise.

Scripted a sample child table (DocumentDistribution) and parent table (DocumentSource):

ALTER TABLE [dbo].[DocumentDistribution] WITH NOCHECK ADD CONSTRAINT [fk_documentdistsourceid_documentsourceid] FOREIGN KEY([DocumentDistSourceID]) REFERENCES [dbo].[DocumentSource] ([DocumentSourceID]) 
GO
 ALTER TABLE [dbo].[DocumentDistribution] CHECK CONSTRAINT [fk_documentdistsourceid_documentsourceid]

Doesnt that tell me that the tables are in relationships and explicitely defined the foreign key constraints?

Aaron Bertrand · Accepted Answer · 2014-10-02T20:51:44.567

This should only be possible if you have a relationship between tables that you know about but SQL Server doesn't.

SELECT fk 
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Now, in the future, define this relationship explicitly, then people won't be able to delete the parent rows while child rows still exist. To delete the rows that shouldn't be there today:

DELETE c
  FROM dbo.ChildTable AS c
  WHERE NOT EXISTS
  (
    SELECT pk FROM dbo.ParentTable AS p
    WHERE p.pk = c.fk
  );

Next, take away their rights to disable these constraints so that they stop doing this the wrong way.

You can also do the opposite action by switching child and parent tables. This would delete all parent rows that are not referred to by the child table - orphan parents, if you will. — Jules, Sep 28 '15 at 18:41

How can I get all orphan records in a single query?

1 Answers1