Select from all tables

I have a lot of tables in my data base all with same structure. I want to select from all tables without having to list开发者_JS百科 them all like so:

SELECT name FROM table1,table2,table3,table4

And I tried but this doesn't work:

SELECT name FROM *

Is there a way to select all tables in a database without listing each table in the query?

i am working on a online file browser, each directory has its own table

It is very unuseful due to one reason: when you have about 200 files (this situation is real, yeah?) you have about 200 tables. And if there are about thousand files in each directory.. etc. In some time you will either have slow processing while selecting from your database either have to buy more server resources.

I think you should change your database structure: just begin from adding parent_folder_id column to your table, after this you can put all your rows (files and directories -- because directory is a file too -- here you can add type column to determine this) into the one table.

As far as I know there are no such wildcards to select from *all tables. I would recommend writing a view and then call that view instead (it will save you writing out the names every time) – VoodooChild

That means you should not have a lot of tables with same structure at all.
But just one table with a field to distinguish different kinds of data, whatever it is.

Then select all would be no problem.

I found a solution, but I would still like to know if there is a simpler way or a better solution.

But here's what I came up with:

$tables = mysql_query("show tables");
$string = '';
while ($table_data = mysql_fetch_row($tables)){
    $string.=$table_data[0].',';
}   
$ALL_TABLES = substr($string,0,strlen($string)-1);
$sql="SELECT name FROM $ALL_TABLES ";

Sounds like you want to UNION together each table, so you get the results as if they were one big table. You'll need to write out the query in full like

SELECT * FROM table1 UNION SELECT * FROM table2 UNION ... SELECT * FROM tableN

Copy & paste may be your friend here.

I'm curious as to why you have lots of different tables with the same structure?

You can generate SELECT by cursor like this code and find all result step by step in sql server:

--Author: Ah.Ghasemi

Declare @Select sysname;

DECLARE A CURSOR  
    FOR Select 'select ' + '*' + ' from ' + name 
        from sys.tables
        --Where  name like 'tbl%'
        Order by name
OPEN A 
FETCH NEXT FROM A INTO @Select
    While (@@FETCH_STATUS <>-1)
        Begin
            exec sp_executesql @Select

            FETCH NEXT FROM A INTO @Select;
        End

close A
Deallocate A

Please let us know if the problem is not resolved.

I hope you for the best