Is it possible to select from all tables inside the schema? I got all the table names from
select table_name from information_schema.tables
but I am unable to use it to do my query.
You can't do it directly as one can not write a query where the table names are not known (ie. coming from some variable or subquery). But you can build and execute a dynamic SQL for this. For example, if you need the column 'name' from every table, you can do the following:
In this form it won't work however, since you cannot
And, of course, this presumes that you want to select a single (or multiple, but always with the same name) columns from all the tables. If you simply need all the data from every table, then the tables must have the same structure (the same column types in the same order).
This is a workaround based on the above suggested approach.