There is a Knowledge Base article about this, but it is insufficient for versions after the SDE table restructuring. Link
Goal: discover replica versions whose replicas have been unregistered, leaving the versions orphaned.
Method: query the versions (sde.SDE_versions) and the replicas (sde.gdb_items)--those versions with unassociated replicas are orphans.
I can find a relationship between replicas and their versions; the item ObjectID is part of the version name, as shown below.
sde.gdb_items: ObjectID = 43923
sde.SDE_versions: Name = SYNC_SEND_43923_16
But I cannot find where this same number '43923' exists on its own such that I am able to join the versions to the replicas to discover orphaned replica versions. You can't join '43923' to 'SYNC_SEND_43923_16' and the alternative is a tedious series of LIKE/IN statements, and there must be a way the db itself correlates them.
Any ideas?
Here is the SQL I use to find replicas:
SELECT * FROM
[ESRIPROD].[sde].[GDB_ITEMS]
where type IN('5B966567-FB87-4DDE-938B-B4B37423539D',
'D86502F9-9758-45C6-9D23-6DD1A0107B47',
'4ED4A58E-621F-4043-95ED-850FBA45FCBC')