Finding entity/webform fields in the database

Question

A bunch of user accounts were deleted and recreated with new UIDs, and so any webform submissions or entities that have fields pointing to the old UID are now broken/wrong. I already updated some UID columns to the new UID, but now I need to find other tables/fields that still point to the old UIDs (and the columns aren't called "UID").

Given the ID of an entityform or webform submission, how can I find which tables/columns in the database correspond to the fields that make up that entityform or submission?

As an example, say I have an entityform with entityform_id = 123 and type = 'my_report'. If I view the entity using the Drupal interface, I see a user field that is blank, because it points to a user's old UID. How do I find where that field is located in the database? Is there a mapping from type 'my_report' to a list of fields or tables?

Also of note is that I need to do this for a decent amount of types of entityforms and webforms, so it'd be nice if I could query for them all, rather than looking at each field of each form using the interface or something.

Answer 1

I used a query similar to this to help me find a list of tables and columns I needed to update:

SELECT DISTINCT
    table_name, 
    column_name
FROM
    INFORMATION_SCHEMA.COLUMNS 
WHERE
    column_name LIKE '%user%_target_id'
    AND (table_name LIKE 'field_data_field%'
    OR table_name LIKE 'field_revision_field%')

It's not ideal, and it still missed a couple tables.

I put the resulting list of tables and columns into a Python script and generated the SQL update statements I needed to run.

There is probably a better way to do it.