How to get all tables with related data for foreignKey value in MySQL

user_6720937 0
Viewed 146

I do have a lot of tables. In every table there is a user_id as foreign_key pointing to user.id (user table, primary key). This is for recording which user inserted/changed data.

Now I want to know in which tables a user inserted data.

Is there a simple trick without joining all tables?

  • I dont expect the data (can query that later)
  • I dont need to know to which tables the foreign_key points to the user table (I already know)
  • I want to know where user data is related

I could join all Tables, but this would be a huge performance issue. There are a lot tables and some are big, too.

I'm looking for something like:

SELECT tablename 
FROM information_schema.foo 
WHERE foreign_key = "key_name" AND foreign_key_value = 2;
mariadb mysql
1 Answers

I don't think there is any direct way, but you can use information_schema.key_column_usage to build queries for you.

SELECT 
  concat('select \'',TABLE_NAME,
  '\' as `in_table`, IF (',COLUMN_NAME,' IS NOT NULL, \'Present\', \'Absent\') as exist from ',
 TABLE_NAME, ' where ', COLUMN_NAME, ' = 2; ') as `query`
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = 'testdb' AND
  REFERENCED_TABLE_NAME = 'users';

Now you have above query that will return queries for each table for user-id = 2. You can create a procedure that take user-id as argument then execute above query.

Loop through the result something like:

select 'employees' as `in_table`, IF (user_id IS NOT NULL, 'Present', 'Absent') as exist from employees where user_id = 2; 
select 'profile_documents' as `in_table`, IF (user_id IS NOT NULL, 'Present', 'Absent') as exist from profile_documents where user_id = 2;

and execute them one-by-one.

Then use the procedure to return the names of tables where user's info is present.

user_25858420
Related