No SQL queries allowed for that. Informatica never publishes SQL queries to change repository contents, so that's completely ruled out; Informatica clearly forbids mingling with the repository contents "manually".
Having written that, in one of my recent projects we've implemented that by some completely different approach. We were facing the same issues as you (DeleteObject doesn't work with versioned repositories, PurgeObject cannot remove all kinds of objects, and so on).
In the end we went a completely different road. For each folder to be deployed (or, in your case, kept), we create an XML export of the contents we want to keep (in our case based on workflows to be deployed), create a new folder, import the XML file into this folder, validated everything, and - in case validation was 100% successful - removed the original folder and renamed the newly created folder to the original folder name.
This can be completely automated, and it will remove many of the old objects.
Thanks for the reply!
Sorry, that bit about deleting stuff from the repository was with tongue firmly in cheek out of frustration for something I thought would be pretty straight-forward - I'll just go find and script up the pmrep commands for this... yeah, no, not so much. And in spite of being told I could find a way to use PurgeVersion for this, I've not found that to be true yet.
Interesting approach... hadn't thought of it yet in spite of doing something like that for YEARS with relational tables when the quantity to delete far outweighs what you need to keep. The only "downside" I can see initially would be a loss of all history on the saved objects, they would all look to be brand new all over again. I'll certainly keep that in mind as an option, however.
Regarding the version history there's one thing to keep in mind:
While the repository contents (after having copied most recent objects to the newly created folder) look as if there's no history, you can simply keep the XML export as the history. Meaning you only keep a history of objects which actually have been deployed, no more, no less.
Isn't that a nice prospect? A clean history, no objects missing, no superfluous objects?