Have you looked into querying INFORMATION_SCHEMA.Tables? As in
SELECT ic.Table_Name,
ic.Column_Name,
ic.data_Type,
IFNULL(Character_Maximum_Length,'') AS `Max`,
ic.Numeric_precision as `Precision`,
ic.numeric_scale as Scale,
ic.Character_Maximum_Length as VarCharSize,
ic.is_nullable as Nulls,
ic.ordinal_position as OrdinalPos,
ic.column_default as ColDefault,
ku.ordinal_position as PK,
kcu.constraint_name,
kcu.ordinal_position,
tc.constraint_type
FROM INFORMATION_SCHEMA.COLUMNS ic
left outer join INFORMATION_SCHEMA.key_column_usage ku
on ku.table_name = ic.table_name
and ku.column_name = ic.column_name
left outer join information_schema.key_column_usage kcu
on kcu.column_name = ic.column_name
and kcu.table_name = ic.table_name
left outer join information_schema.table_constraints tc
on kcu.constraint_name = tc.constraint_name
order by ic.table_name, ic.ordinal_position;
You can create a stored procedure and put the table names in a cursor, then loop through your table names to show the data.
Getting started with stored procedure: http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx
Creating a cursor: http://www.mysqltutorial.org/mysql-cursor/
For example,
CREATE PROCEDURE `ShowFromTables`()
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE c_table varchar(100) DEFAULT "";
DECLARE table_cursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_name like 'wp_1%';
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN table_cursor;
get_data: LOOP
FETCH table_cursor INTO c_table;
IF v_finished = 1 THEN
LEAVE get_data;
END IF;
SET @s=CONCAT("SELECT * FROM ",c_table,";");
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP get_data;
CLOSE table_cursor;
END
Then call the stored procedure:
CALL ShowFromTables();
Yes, SELECT from table_schema could be very usefull for system administration. If you have lot of servers, databases, tables... sometimes you need to DROP or UPDATE bunch of elements. For example to create query for DROP all tables with prefix name "wp_old_...":
SELECT concat('DROP TABLE ', table_name, ';') FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '*name_of_your_database*'
AND table_name LIKE 'wp_old_%';
in MySql 5.1 you can try
show tables like 'user%';
output:
mysql> show tables like 'user%';
+----------------------------+
| Tables_in_test (user%) |
+----------------------------+
| user |
| user_password |
+----------------------------+
2 rows in set (0.00 sec)
You may be closer than you think — SHOW TABLES already behaves a lot like SELECT:
$pdo = new PDO("mysql:host=$host;dbname=$dbname",$user,$pass);
foreach ($pdo->query("SHOW TABLES") as $row) {
print "Table $row[Tables_in_$dbname]\n";
}
I don't understand why you want to use SELECT * FROM as part of the statement.
I think you want SELECT * FROM INFORMATION_SCHEMA.TABLES
See http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
Not that I know of, unless you select from INFORMATION_SCHEMA, as others have mentioned.
However, the SHOW command is pretty flexible,
E.g.:
SHOW tables like '%s%'
I think you want SELECT * FROM INFORMATION_SCHEMA.TABLES
See http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
Have you looked into querying INFORMATION_SCHEMA.Tables? As in
SELECT ic.Table_Name,
ic.Column_Name,
ic.data_Type,
IFNULL(Character_Maximum_Length,'') AS `Max`,
ic.Numeric_precision as `Precision`,
ic.numeric_scale as Scale,
ic.Character_Maximum_Length as VarCharSize,
ic.is_nullable as Nulls,
ic.ordinal_position as OrdinalPos,
ic.column_default as ColDefault,
ku.ordinal_position as PK,
kcu.constraint_name,
kcu.ordinal_position,
tc.constraint_type
FROM INFORMATION_SCHEMA.COLUMNS ic
left outer join INFORMATION_SCHEMA.key_column_usage ku
on ku.table_name = ic.table_name
and ku.column_name = ic.column_name
left outer join information_schema.key_column_usage kcu
on kcu.column_name = ic.column_name
and kcu.table_name = ic.table_name
left outer join information_schema.table_constraints tc
on kcu.constraint_name = tc.constraint_name
order by ic.table_name, ic.ordinal_position;
I think you want SELECT * FROM INFORMATION_SCHEMA.TABLES
See http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
You can't put SHOW statements inside a subquery like in your example. The only statement that can go in a subquery is SELECT.
As other answers have stated, you can query the INFORMATION_SCHEMA directly with SELECT and get a lot more flexibility that way.
MySQL's SHOW statements are internally just queries against the INFORMATION_SCHEMA tables.
User @physicalattraction has posted this comment on most other answers:
This gives you (meta)information about the tables, not the contents of the table, as the OP intended. – physicalattraction
On the contrary, the OP's question does not say that they want to select the data in all the tables. They say they want to select from the result of SHOW TABLES, which is just a list of table names.
If the OP does want to select all data from all tables, then the answer is no, you can't do it with one query. Each query must name its tables explicitly. You can't make a table name be a variable or the result of another part of the same query. Also, all rows of a given query result must have the same columns.
So the only way to select all data from all tables would be to run SHOW TABLES and then for each table named in that result, run another query.
I think what you want is MySQL's information_schema view(s): http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
SELECT * FROM INFORMATION_SCHEMA.TABLES
That should be a good start. For more, check INFORMATION_SCHEMA Tables.
You may be closer than you think — SHOW TABLES already behaves a lot like SELECT:
$pdo = new PDO("mysql:host=$host;dbname=$dbname",$user,$pass);
foreach ($pdo->query("SHOW TABLES") as $row) {
print "Table $row[Tables_in_$dbname]\n";
}
Have you looked into querying INFORMATION_SCHEMA.Tables? As in
SELECT ic.Table_Name,
ic.Column_Name,
ic.data_Type,
IFNULL(Character_Maximum_Length,'') AS `Max`,
ic.Numeric_precision as `Precision`,
ic.numeric_scale as Scale,
ic.Character_Maximum_Length as VarCharSize,
ic.is_nullable as Nulls,
ic.ordinal_position as OrdinalPos,
ic.column_default as ColDefault,
ku.ordinal_position as PK,
kcu.constraint_name,
kcu.ordinal_position,
tc.constraint_type
FROM INFORMATION_SCHEMA.COLUMNS ic
left outer join INFORMATION_SCHEMA.key_column_usage ku
on ku.table_name = ic.table_name
and ku.column_name = ic.column_name
left outer join information_schema.key_column_usage kcu
on kcu.column_name = ic.column_name
and kcu.table_name = ic.table_name
left outer join information_schema.table_constraints tc
on kcu.constraint_name = tc.constraint_name
order by ic.table_name, ic.ordinal_position;
Not that I know of, unless you select from INFORMATION_SCHEMA, as others have mentioned.
However, the SHOW command is pretty flexible,
E.g.:
SHOW tables like '%s%'
in MySql 5.1 you can try
show tables like 'user%';
output:
mysql> show tables like 'user%';
+----------------------------+
| Tables_in_test (user%) |
+----------------------------+
| user |
| user_password |
+----------------------------+
2 rows in set (0.00 sec)
SELECT * FROM INFORMATION_SCHEMA.TABLES
That should be a good start. For more, check INFORMATION_SCHEMA Tables.
To count:
SELECT COUNT(*) as total FROM (SELECT TABLE_NAME as tab, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY tab) tables;
To list:
SELECT TABLE_NAME as table, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY table;
You can create a stored procedure and put the table names in a cursor, then loop through your table names to show the data.
Getting started with stored procedure: http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx
Creating a cursor: http://www.mysqltutorial.org/mysql-cursor/
For example,
CREATE PROCEDURE `ShowFromTables`()
BEGIN
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE c_table varchar(100) DEFAULT "";
DECLARE table_cursor CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_name like 'wp_1%';
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN table_cursor;
get_data: LOOP
FETCH table_cursor INTO c_table;
IF v_finished = 1 THEN
LEAVE get_data;
END IF;
SET @s=CONCAT("SELECT * FROM ",c_table,";");
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP get_data;
CLOSE table_cursor;
END
Then call the stored procedure:
CALL ShowFromTables();
Not that I know of, unless you select from INFORMATION_SCHEMA, as others have mentioned.
However, the SHOW command is pretty flexible,
E.g.:
SHOW tables like '%s%'
SELECT column_comment FROM information_schema.columns WHERE table_name = 'myTable' AND column_name = 'myColumnName'
This will return the comment on: myTable.myColumnName
SELECT * FROM INFORMATION_SCHEMA.TABLES
That should be a good start. For more, check INFORMATION_SCHEMA Tables.
To count:
SELECT COUNT(*) as total FROM (SELECT TABLE_NAME as tab, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY tab) tables;
To list:
SELECT TABLE_NAME as table, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY table;
Have you looked into querying INFORMATION_SCHEMA.Tables? As in
SELECT ic.Table_Name,
ic.Column_Name,
ic.data_Type,
IFNULL(Character_Maximum_Length,'') AS `Max`,
ic.Numeric_precision as `Precision`,
ic.numeric_scale as Scale,
ic.Character_Maximum_Length as VarCharSize,
ic.is_nullable as Nulls,
ic.ordinal_position as OrdinalPos,
ic.column_default as ColDefault,
ku.ordinal_position as PK,
kcu.constraint_name,
kcu.ordinal_position,
tc.constraint_type
FROM INFORMATION_SCHEMA.COLUMNS ic
left outer join INFORMATION_SCHEMA.key_column_usage ku
on ku.table_name = ic.table_name
and ku.column_name = ic.column_name
left outer join information_schema.key_column_usage kcu
on kcu.column_name = ic.column_name
and kcu.table_name = ic.table_name
left outer join information_schema.table_constraints tc
on kcu.constraint_name = tc.constraint_name
order by ic.table_name, ic.ordinal_position;
Yes, SELECT from table_schema could be very usefull for system administration. If you have lot of servers, databases, tables... sometimes you need to DROP or UPDATE bunch of elements. For example to create query for DROP all tables with prefix name "wp_old_...":
SELECT concat('DROP TABLE ', table_name, ';') FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = '*name_of_your_database*'
AND table_name LIKE 'wp_old_%';
I don't understand why you want to use SELECT * FROM as part of the statement.
You can't put SHOW statements inside a subquery like in your example. The only statement that can go in a subquery is SELECT.
As other answers have stated, you can query the INFORMATION_SCHEMA directly with SELECT and get a lot more flexibility that way.
MySQL's SHOW statements are internally just queries against the INFORMATION_SCHEMA tables.
User @physicalattraction has posted this comment on most other answers:
This gives you (meta)information about the tables, not the contents of the table, as the OP intended. – physicalattraction
On the contrary, the OP's question does not say that they want to select the data in all the tables. They say they want to select from the result of SHOW TABLES, which is just a list of table names.
If the OP does want to select all data from all tables, then the answer is no, you can't do it with one query. Each query must name its tables explicitly. You can't make a table name be a variable or the result of another part of the same query. Also, all rows of a given query result must have the same columns.
So the only way to select all data from all tables would be to run SHOW TABLES and then for each table named in that result, run another query.
SELECT column_comment FROM information_schema.columns WHERE table_name = 'myTable' AND column_name = 'myColumnName'
This will return the comment on: myTable.myColumnName
I don't understand why you want to use SELECT * FROM as part of the statement.
I think what you want is MySQL's information_schema view(s): http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
I don't understand why you want to use SELECT * FROM as part of the statement.
I think you want SELECT * FROM INFORMATION_SCHEMA.TABLES
See http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
I think what you want is MySQL's information_schema view(s): http://dev.mysql.com/doc/refman/5.0/en/tables-table.html
SELECT * FROM INFORMATION_SCHEMA.TABLES
That should be a good start. For more, check INFORMATION_SCHEMA Tables.
Source: Stackoverflow.com