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

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?

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.

StackExchange Database Administrators Q#78271, answer score: 27