I have several databases that have tables with the same name.
I mean, I got the following databases
mydb1, mydb2, etc
and each mydb has a table called myTable. But these tables have diffenet columns:
myTable in mydb1
id, name, age
myTable in mydb2
id, name, street, size
Now, if I had only one Database mydb1 then I also had only one Table MyTable. If I wanted to know all the columns in that table I would make the following sql request
use information_schema;
select column_name from columns where table_name = 'myTable';
In that case this works fine, I get the columns I'm looking for.
But, in my case I have several tables called MyTable in different Databases. And the same sql request yields:
id, name, age, id, name, street, size
But I just want to have the columns of mydb1.myTable.
How can I get only the columnnames from just one myTable?
Hint: I just want to have the column names not the type, nulls oder keys. Therefore the request show columns from mydb1.myTable is not what I'm looking for.
The TABLE_SCHEMA column in the columns table contains the database name, so you can do it like this:
select column_name from columns where table_name = 'myTable' AND table_schema = 'mydb1';