How do I find all tables that reference a particular foreign key column name of a particular table?

[ptelerez1]

I haven’t been able to locate a solution in the forum as of yet. I’m attempting to develop a query that lists all the tables that reference a particular column of a particular table. I haven’t been able to find anything that works for me in any of my Google searches either. Psuedo example: ’ find * tables that use receipt_id foreign key from account_billing table’. Any ideas? Thanks in advance!

[igor_g]

In MYSQL: database “information_schema”, table “KEY_COLUMN_USAGE”.

And anyway, if you need some meta information, use this database.

Also in other DB servers there are a similar databases.

[ptelerez1]

Hey! Thanks for taking the time and for your response. Perhaps I’m misunderstanding the reply. Attempted your suggestion in my console and got the following:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database “information_schema” table “KEY_COLUMN_USAGE”' at line 1

`

[r937]

could you please show the sql statement that produced this error

[ptelerez1]

I’ve tried:
database “information_schema”, table “KEY_COLUMN_USAGE”;

then:
my db name “information_schema”, table “KEY_COLUMN_USAGE”;

Thanks!

[ptelerez1]

Before this post I was trying:
SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = ‘account_payment’ AND REFERENCED_COLUMN_NAME = ‘receipt_id’ AND TABLE_SCHEMA = ‘my db here’;
But wasn’t working for me either…

[r937]

@ptelerez1

But wasn’t working for me either…

you’re on the right path with SELECT… not sure what that other stuff was, but it isn’t SQL

what does “wasn’t working” mean? what was the error messaage?

[m_hutley]

@r937

not sure what that other stuff was, but it isn’t SQL

it was “take what was said in post #2 and stick it into a box”.

I’m going to guess that the error message was “no such table KEY_COLUMN_USAGE”.

The select query would work, if it was executed against the information_schema database. Try SELECT * FROM information_schema.KEY_COLUMN_USAGE ...