How to select all tables containing the field with the same name?

Asked

Viewed 4,041 times

1

I need to search all fields "id_category" in a given database, and bring a list of the tables that contain this field in common, which in this case is the foreign key of the table "sis_category".

mysql sql relationship dql ddl

1 answer

3


The way I solved it was by doing the following:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'meu_banco' and COLUMN_NAME='id_category';

And to bring only the table name:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'meu_banco' and COLUMN_NAME='id_category';

And to check the records in each of the tables, I ran SQL below:

SELECT CONCAT(CONCAT('SELECT * from ',TABLE_NAME),'
WHERE id_category=39;')
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'meu_banco'
AND COLUMN_NAME='id_category'

Browser other questions tagged mysql sql relationship dql ddl

You are not signed in. Login or sign up in order to post.