selecting first column from all tables in a mysql database

Question

i have a column named "name" which is present in all tables in mysql database.

I wanted to list all the names in all tables so i used the following query

select name from (SELECT table_name FROM information_schema.tables WHERE table_type='BASE TABLE') as abc

But it did not work for me and instead it returned me the table_name column alone.

Then i used show tables and stored the output in another table called table_list then i executed the following query

select name from (select table_name from table_list) as abc

This also returned me the same result i.e. all table names.

Can i know what is that i am doing wrong and what is the right way to do it?

I am using MySQL 5.4 and i want to either write a subquery or a procedure or a function purely in mysql.

Answer 1

What you need is a way to have variables in SQL (so you can say select * from $name where $name in (select ...)). SQL doesn't allow that.

My suggestion is to split the process up:

First run this query:

select 'select distinct name from ' || table_name || ' union'
from select table_name from table_list

That'll give you the selects to run. Put them into a small script, remove the last "union" and run that script.

[EDIT] If MySQL supports an "eval" operator in stored procedures (i.e. where you can build SQL from parts and then run it), you could do this. I checked the docs and it doesn't look like there is an eval. You could also write an extension in C (see chapter 22) that either implements the lookup or an "eval" function.

But my guess is that your database won't change all the time. So the most simple solution would be to write a small SQL script that creates the code for a view (that is a string; it doesn't actually create the view ). Every time the DB changes, you simply run the script to recreate the view and afterwards, you can run the query against the view to get a list of all names.

Answer 2

There is PREPARE and EXECUTE which can run a sql statement from inside a user variable, so could probably use something similar to (untested!)

SET @a = "";

SELECT @a := CONCAT('(select name from ',GROUP_CONCAT(table_name SEPARATOR ') UNION (select name from '),')') FROM information_schema.tables WHERE table_type='BASE TABLE' GROUP BY 1;

PREPARE stmt FROM @a;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;