How to find orphaned records by query on sys_attachment_doc table

[bunnykiran94]

Hi.

Can anyone help me in how to query the orphaned records on attachment document table? I have a requirement to pull all the orphaned records from sys_attachment_doc and clean up them.

[bernyalvarado]

If you're still interesting to dig and explore... with some semi-automated process... here goes some tips:

a) first go to the sys_attachment table (type sys_attachment.list in the navigation window and hit enter)

b) each record in the attachment table will have a sys_id. You can pull one of those sys_id and execute a script that searches through the instance tables for that particular sys_id. This could be a very resource consuming query. I strongly recommend you do the query one sys_id at a time, perhaps running it from a background script. The following blog post has a snippet of code that you can use to find a record's table given only the sys_id.

c) review each one of the tables associated to the attachment. Don't assume anything. Make sure you're truly deleting an attachment that was entered previously by a user.

d) finally... don't try to do this in production first. Clone production to a sub-production instance and identify and practice which records you want to delete at a sub-production instance first.

I hope this helps... and please be careful! If you're not sure what you're doing, you can always call an expert consultant or even engage Hi to help you out. (actually... that may be the best route... open a Hi ticket! 🙂 They may have a best practice on how to approach this)

Thanks,

Berny

[bernyalvarado]

I hope this helps! 🙂

[sonnydee]

I think you have to first define what you mean by orphaned records.

sys_attachment has these 2 columns to identify which record is the attachment linked to: Table Name and Table Sys ID

You can maybe run a script first searching on the Incident table, using the Table Sys ID and if the incident record is not found, then its an orphaned record candidate.

you also need to look into the table called sys_attachment_doc, which is where the actual attachment is stored.  You may see that there will be more than 1 sys_attachment_doc record for one sys_attachment record. This is especially for large attachments.

lastly, you mentioned "cleaning up", please do practice caution when attempting to delete records, especially if you will be going the scripting route.

i hope this helps and good luck.

[bernyalvarado]

Hi, do you have any further questions? Do you believe you can mark the responses as helpful/correct so that we can close this thread?

Thanks,

Berny