Magento 1.9 / OpenMage: How To Merge Customers

Question

My client has a requirement to merge customers in their Magento 1.9 (now migrated to OpenMage) store. On the surface, it seems pretty straightforward.

What I Need To Achieve

1. Choose the customer that you want to keep.
2. Get the ID(s) of the other customers.
3. Change the customer_id in every table to reference the customer that you want to keep.
4. Delete the other customer records.

Finding The Tables / Columns

However, it's not that simple. Why? Any extension could also reference the customer_id in their custom tables, as demonstrated by this query on your database.

SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('customer_id','ColumnB') AND TABLE_SCHEMA='[your-magento-db-name]';

This will list all of the database tables that have the customer_id field, and that's assuming that the extension vendor actually used the name customer_id for their referencing field.

+-------------------------------+
| TABLE_NAME                    |
+-------------------------------+
| activity_event                | <- added by other extension
| catalog_compare_item          |
| downloadable_link_purchased   |
| gift_message                  |
| log_customer                  |
| log_visitor_online            |
| mailchimp_interest_group      | <- added by [Mailchimp extension][1]
| newsletter_subscriber         |
| oauth_token                   |
| persistent_session            |
| poll_vote                     |
| product_alert_price           |
| product_alert_stock           |
| rating_option_vote            |
| report_compared_product_index |
| report_viewed_product_index   |
| review_detail                 |
| sagepaysuite_tokencard        | <- added by [Sagepay extension][2]
| sales_billing_agreement       |
| sales_flat_order              |
| sales_flat_order_address      |
| sales_flat_order_grid         |
| sales_flat_quote              |
| sales_flat_quote_address      |
| sales_flat_shipment           |
| sales_recurring_profile       |
| salesrule_coupon_usage        |
| salesrule_customer            |
| stripe_customers              | <- added by [Stripe extension][3]
| tag_relation                  |
| wishlist                      |
+-------------------------------+

However, this misses out on tables that do not use customer_id as their referencing field. Let's try searching for the customer_entity.entity_id...

SELECT TABLE_NAME, COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'customer_entity' AND REFERENCED_COLUMN_NAME = 'entity_id' AND TABLE_SCHEMA = '[your-magento-db-name]';

This time we find all referencing column results:

+-------------------------------+-------------------+
| TABLE_NAME                    | COLUMN_NAME       |
+-------------------------------+-------------------+
| activity_event                | customer_id       | <- added by other extension
| catalog_compare_item          | customer_id       |
| customer_address_entity       | parent_id         |
| customer_entity_datetime      | entity_id         |
| customer_entity_decimal       | entity_id         |
| customer_entity_int           | entity_id         |
| customer_entity_text          | entity_id         |
| customer_entity_varchar       | entity_id         |
| downloadable_link_purchased   | customer_id       |
| oauth_token                   | customer_id       |
| persistent_session            | customer_id       |
| product_alert_price           | customer_id       |
| product_alert_stock           | customer_id       |
| report_compared_product_index | customer_id       |
| report_viewed_product_index   | customer_id       |
| review_detail                 | customer_id       |
| sales_billing_agreement       | customer_id       |
| sales_flat_order              | customer_id       |
| sales_flat_order_grid         | customer_id       |
| sales_recurring_profile       | customer_id       |
| salesrule_coupon_usage        | customer_id       |
| salesrule_customer            | customer_id       |
| tag                           | first_customer_id |
| tag_relation                  | customer_id       |
| wishlist                      | customer_id       |
+-------------------------------+-------------------+
29 rows in set (0.00 sec)

However, this is still not exhaustive because some extensions might call the customer_id column "something else" in their table(s) and not reference the customer_entity with a FK.

Logical Solution

The logical solution here is to write an extension.

This suggestion steps through some of the referencing tables. However, it's not thorough enough and misses quite a lot of other tables.

So What's The Question

I'm happy to write an extension and solve this. Before I do. Has anyone done this successfully and managed to cover all possible tables?