I am trying to select all rows from all tables in the database when a column equal a given name. I have like many tables with same structure and columns. I have written this piece of code but it throws an error.
SELECT GROUP_CONCAT(qry SEPARATOR ' UNION ') INTO @sql FROM ( SELECT CONCAT('SELECT * FROM `',table_name,'` where Name like ''','sally%','''') qry FROM ( SELECT distinct table_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_schema = 'db_customers' AND column_name LIKE 'Name' ) A ) B;
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
This is the error I am getting. I am aware that union will only work if you have the same number of columns in all tables and I run a test and my all tables have same columns.
15:12:53 PREPARE s FROM @sql Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ': like 'sally%' UNION SELECT * FROM
customer where Name: like 'sally%' UNION S' at line 1 0.000 sec