Search code examples
mysqldatabasereferenceforeign-keys

Search for a deleted table's reference id in all tables, rows and columns of a DB

I had created a table in my DB and then deleted whole table. but now I got error of Foreign Key constraint of that table id. And due to it I am unable to update any other thing for my app in local.

I am having 400+ tables in my database with thousands of records.

How could I find that deleted table's reference id in my all current tables?

Solution

  • You can try to find all the referenced tables for the deleted table in the system database information_schema:

    SELECT 
  kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, kcu.COLUMN_NAME, 
  kcu.REFERENCED_TABLE_NAME, kcu.REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE kcu
WHERE kcu.REFERENCED_TABLE_SCHEMA = 'YourDatabaseName' AND 
      kcu.REFERENCED_TABLE_NAME = 'YourDeletedTableName'