How can I find every record that references a particular record?

[geoffcox]

We are trying to consolidate our groups (sys_user_group), but often when we deactivate one we find out later that some object in the system was referencing that group.

I'm considering writing a brute force script that will go through the cmdb for every field on every table to see if it is a reference field, and if so if it references the sys_user_table, and if so, if it matches the particular record I care about!

Is there something that already does this?

[geoffcox]

I meant sys_user_group table.

[kristenankeny]

We had a similar situation and I ended up adding a bunch of related lists to our group records to show all the places the group could be referenced. Anytime someone asks us to retire a group, we check those tabs and get clarification on what group should replace the retired one prior to retiring. If there is something better OOB, it'd be great to know. In a similar vein, we get our user updates from active directory and wanted to be sure that our cmdb is kept up to date, so I wrote a business rule that culls the database for all the fields/tables I care about and to create a task for our admin group to follow up (remove the user from groups, remove roles, and update the cmdb where needed).

[geoffcox]

Thanks, and yes, I considered that, but we have one object in particular that references the group table something like 50 times (it holds all the various assignment groups for routing incidents and change tasks per product). So I would have to add 50 related lists to the group form! And that's just one of the objects that references the group table! There are many more.

[kristenankeny]

You could build a custom relationship that iterates through all those fields and finds the records with the group referenced in any of them. It might make the related list option a little cleaner.