Script/SQL comman(s) for querying for orphaned images/attachments?

[LOCO.MTB]

I just found a 2.7MB+ pdf file stired in 'filedata' table that was orphaned.... IAW had NO reference anywhere else... not in 'attachment' table, et al. As far as I can tell, unless the file is listed in 'attachment', you can't find it in CP or anywhere else except by accident using phpMyAdmin, et al. database browser.

So... if there is one orphan attachment, there is likely more... and I want to clean 'em out. I'm not a DBA or a SQL guy, so can anyone help out?

Looks like to me what needs to happen is that I need to run an outside loop choosing each filedataid one at a time, and then see if there is a corresponding record in attachment with the same filedataid... and for any record in filedata that doesn't have a corresponding record in attachment, the record is added to a list of records to display as the query result.

Anyone want to take a stab at helping me construct such a query/tool?

Thanks!

[kh99]

If what you say is correct, then I think you should be able to do something like this:

SELECT * FROM filedata LEFT JOIN attachment USING(filedataid) WHERE attachmentid IS NULL

(of course if you have a table prefix you need to add it to the table names). And you can change it to a DELETE if you're feeling confident.

But looking at the database, I find a filedataid field in tables filedata and attachment, but also in attachmentcategoryuser, picturecomment, and picturecomment_hash, and I don't know exactly what those are for, so I don't know if some filedata can be associated with something else without having an attachment record. If not, it seems like you'd at least want to remove those rows as well.