Mysql statement to list tables of certain columns?

Question

I'm trying to figure out the foreign keys relationship within my MySQL databases.

I have many tables that has the "mydomain_id" column located in other tables.

Is there a command line I can use to list all tables that has that particular column?

Nope, no such thing exists, sorry. The only way I see is a pretty crude workaround: you could use the `mysqldump` utility with the no-data flag to dump the table structures and pipe the output into a `grep` call to filter by that column... — arkascha, Nov 22 '15 at 21:04
Well, you could use the information schema: https://dev.mysql.com/doc/refman/5.0/en/columns-table.html — Strawberry, Nov 22 '15 at 21:07

score 1 · Accepted Answer · answered Nov 22 '15 at 21:17

SELECT
    DISTINCT `table_name`
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    COLUMN_NAME IN ('name') AND
    TABLE_SCHEMA='symfony_backend';

Gives: (all the tables listed below exist in my database symfony_backend)

table_name
'child'
'country'
'league'
'parent'
'player'
'schools'
'student'
'team'
'user'

You can feed more column names with: COLUMN_NAME IN ('name', 'another_column', 'and_another_one')

score 0 · Answer 2 · answered Nov 22 '15 at 21:17

0

SELECT TABLE_NAME
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_schema = 'db_name'
    AND column_name = 'mydomain_id'

answered Nov 22 '15 at 21:17

Strawberry

1

A code-only question is considered low quality. A brief description of exactly what the code does would be helpful. – Bruce P Nov 22 '15 at 21:41
Maybe when you've earned it – Strawberry Nov 22 '15 at 23:17

2 Answers2