I need to look up all the "id_category" fields in a particular database, and bring up a list of tables that contain this field in common, which in this case is the foreign key of the "sis_category" table.
How to select all the tables that contain the field with the same name?
0
asked by anonymous 08.04.2016 / 21:36
1 answer
0
The way I solved this, I did 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 executed the 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'
08.04.2016 / 21:36