MYSQL select all rows from all tables

[Carlos_18]

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
Thanks

[pty]

If you have a problem like this you're probably better just starting again. This is a bad idea that will generate useless data. Burn it with fire.

[flashx4u]

Hello Carlos, I' don't know if this is a great Design but you could solve it by first get the name of the table and columns and generate a new SQL statement that you could use later

Like this:
eg, look in all Tables that have an Oid and A Name Column:

SELECT distinct 'SELECT ''' + c1.table_name + ':'' , [Oid], [Name] FROM [' + c1.table_name + '] WHERE [Name] LIKE ''sally%'' UNION '
             FROM INFORMATION_SCHEMA.COLUMNS c1
             WHERE  c1.table_schema = 'dbo' AND NOT c1.table_schema = 'sysdiagrams'
              AND c1.column_name = 'Name' AND EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS c2 WHERE c2.table_schema = 'dbo' AND c2.column_name = 'Oid' AND c1.table_name = c2.table_name)

This will generate you some SQL Selects.
e.g:

SELECT 'Standort:' ,[Oid], [Name] FROM [Standort] WHERE [Name] LIKE 'sally%' UNION 
SELECT 'StandortRaum:' ,[Oid], [Name] FROM [StandortRaum] WHERE [Name] LIKE 'sally%' UNION 
SELECT 'Tag:' ,[Oid], [Name] FROM [Tag] WHERE [Name] LIKE 'sally%' UNION 
SELECT 'WebNaviCategory:' ,[Oid], [Name] FROM [WebNaviCategory] WHERE [Name] LIKE 'sally%' UNION 
SELECT 'Word:' ,[Oid], [Name] FROM [Word] WHERE [Name] LIKE 'sally%' UNION 

Just remove the last UNION and run it
The Result will be the Tablename, Oid of the Row and the Name you found

Cities: 2961606 SALLYBROOK
Cities: 3315282 SALLYNOGGIN
Cities: 4503956 SALLYMARSHALLCROSSING

Just an Idea,
I would solve this programmatically with .Net or PHP

Cheers, Helmut

[White_4]

select TOP 11 column 1,column 1 from table name
where id='0000'
group by column 1,column 2
order by id