<p>Is it possible to select from <code>show tables</code> in MySQL?</p>

<pre><code>SELECT * FROM (SHOW TABLES) AS `my_tables`
</code></pre>

<p>Something along these lines, though the above does not work (on 5.0.51a, at least).</p>

[{'answer_id': 64918, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>Have you looked into querying INFORMATION_SCHEMA.Tables?  As in</p>\n\n<pre><code>SELECT ic.Table_Name,\n    ic.Column_Name,\n    ic.data_Type,\n    IFNULL(Character_Maximum_Length,'') AS `Max`,\n    ic.Numeric_precision as `Precision`,\n    ic.numeric_scale as Scale,\n    ic.Character_Maximum_Length as VarCharSize,\n    ic.is_nullable as Nulls, \n    ic.ordinal_position as OrdinalPos, \n    ic.column_default as ColDefault, \n    ku.ordinal_position as PK,\n    kcu.constraint_name,\n    kcu.ordinal_position,\n    tc.constraint_type\nFROM INFORMATION_SCHEMA.COLUMNS ic\n    left outer join INFORMATION_SCHEMA.key_column_usage ku\n        on ku.table_name = ic.table_name\n        and ku.column_name = ic.column_name\n    left outer join information_schema.key_column_usage kcu\n        on kcu.column_name = ic.column_name\n        and kcu.table_name = ic.table_name\n    left outer join information_schema.table_constraints tc\n        on kcu.constraint_name = tc.constraint_name\norder by ic.table_name, ic.ordinal_position;\n</code></pre>\n"}, {'answer_id': 64921, 'author': 'Andrew', 'author_id': 8871, 'author_profile': 'https://Stackoverflow.com/users/8871', 'pm_score': 6, 'selected': False, 'text': '<p>I think you want <code>SELECT * FROM INFORMATION_SCHEMA.TABLES</code></p>\n\n<p>See <a href="http://dev.mysql.com/doc/refman/5.0/en/tables-table.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.0/en/tables-table.html</a></p>\n'}, {'answer_id': 64922, 'author': 'Matt Rogish', 'author_id': 2590, 'author_profile': 'https://Stackoverflow.com/users/2590', 'pm_score': 2, 'selected': False, 'text': '<p>I think what you want is MySQL\'s information_schema view(s):\n<a href="http://dev.mysql.com/doc/refman/5.0/en/tables-table.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.0/en/tables-table.html</a></p>\n'}, {'answer_id': 64945, 'author': 'Sören Kuklau', 'author_id': 1600, 'author_profile': 'https://Stackoverflow.com/users/1600', 'pm_score': 2, 'selected': False, 'text': '<pre><code>SELECT * FROM INFORMATION_SCHEMA.TABLES\n</code></pre>\n\n<p>That should be a good start. For more, check <a href="http://dev.mysql.com/doc/refman/5.0/en/information-schema.html" rel="nofollow noreferrer">INFORMATION_SCHEMA Tables</a>.</p>\n'}, {'answer_id': 64946, 'author': 'Derek B. Bell', 'author_id': 8944, 'author_profile': 'https://Stackoverflow.com/users/8944', 'pm_score': 1, 'selected': False, 'text': '<p>I don\'t understand why you want to use  <code>SELECT * FROM</code> as part of the statement.</p>\n\n<p><a href="http://dev.mysql.com/doc/refman/5.0/en/show-tables.html" rel="nofollow noreferrer">12.5.5.30. SHOW TABLES Syntax</a></p>\n'}, {'answer_id': 64994, 'author': 'Lucas Oman', 'author_id': 6726, 'author_profile': 'https://Stackoverflow.com/users/6726', 'pm_score': 4, 'selected': False, 'text': "<p>Not that I know of, unless you select from <code>INFORMATION_SCHEMA</code>, as others have mentioned. </p>\n\n<p>However, the <code>SHOW</code> command is pretty flexible, \nE.g.:</p>\n\n<pre><code>SHOW tables like '%s%'\n</code></pre>\n"}, {'answer_id': 4108393, 'author': 'Brian', 'author_id': 498605, 'author_profile': 'https://Stackoverflow.com/users/498605', 'pm_score': 2, 'selected': False, 'text': "<pre><code>SELECT column_comment FROM information_schema.columns WHERE table_name = 'myTable' AND column_name = 'myColumnName'\n</code></pre>\n\n<p>This will return the comment on: myTable.myColumnName</p>\n"}, {'answer_id': 8561638, 'author': 'Srdjan', 'author_id': 1105899, 'author_profile': 'https://Stackoverflow.com/users/1105899', 'pm_score': 2, 'selected': False, 'text': '<p>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_...":</p>\n\n<pre><code>SELECT concat(\'DROP TABLE \', table_name, \';\') FROM INFORMATION_SCHEMA.TABLES\nWHERE table_schema = \'*name_of_your_database*\'\nAND table_name LIKE \'wp_old_%\';\n</code></pre>\n'}, {'answer_id': 16576049, 'author': 'MT467', 'author_id': 2183883, 'author_profile': 'https://Stackoverflow.com/users/2183883', 'pm_score': 2, 'selected': False, 'text': "<p>in MySql 5.1 you can try </p>\n\n<pre><code>show tables like 'user%';\n</code></pre>\n\n<p><strong>output:</strong></p>\n\n<pre><code>mysql&gt; show tables like 'user%';\n\n+----------------------------+\n\n| Tables_in_test (user%) |\n\n+----------------------------+\n\n| user                       |\n\n| user_password              |\n\n+----------------------------+\n\n2 rows in set (0.00 sec)\n</code></pre>\n"}, {'answer_id': 16576727, 'author': 'Bill Karwin', 'author_id': 20860, 'author_profile': 'https://Stackoverflow.com/users/20860', 'pm_score': 3, 'selected': False, 'text': "<p>You can't put <code>SHOW</code> statements inside a subquery like in your example. The only statement that can go in a subquery is <code>SELECT</code>.</p>\n\n<p>As other answers have stated, you can query the INFORMATION_SCHEMA directly with <code>SELECT</code> and get a lot more flexibility that way.</p>\n\n<p>MySQL's <code>SHOW</code> statements are internally just queries against the INFORMATION_SCHEMA tables.</p>\n\n<p>User @physicalattraction has posted this comment on most other answers:</p>\n\n<blockquote>\n  <p>This gives you (meta)information about the tables, not the contents of the table, as the OP intended. – physicalattraction</p>\n</blockquote>\n\n<p>On the contrary, the OP's question does <em>not</em> say that they want to select the data in all the tables. They say they want to select from the result of <code>SHOW TABLES</code>, which is just a list of table names.</p>\n\n<p>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.</p>\n\n<p>So the only way to select all data from all tables would be to run <code>SHOW TABLES</code> and then for each table named in that result, run another query.</p>\n"}, {'answer_id': 18019612, 'author': 'Bob Stein', 'author_id': 673991, 'author_profile': 'https://Stackoverflow.com/users/673991', 'pm_score': 3, 'selected': False, 'text': '<p>You may be closer than you think — <a href="https://dev.mysql.com/doc/refman/en/show-tables.html" rel="nofollow noreferrer">SHOW TABLES</a> already behaves a lot like a SELECT statement. Here\'s a <a href="https://www.php.net/manual/en/class.pdo.php" rel="nofollow noreferrer">PHP</a> example of how you might fetch its &quot;rows&quot;:</p>\n<pre class="lang-php prettyprint-override"><code>$pdo = new PDO(&quot;mysql:host=$host;dbname=$dbname&quot;,$user,$pass);\nforeach ($pdo-&gt;query(&quot;SHOW TABLES&quot;) as $row) {\n    print &quot;Table $row[Tables_in_$dbname]\\n&quot;;\n}\n</code></pre>\n<p>SHOW TABLES behaves like a SELECT on a one-column table. That column name is <code>Tables_in_</code> plus the database name.</p>\n'}, {'answer_id': 28628228, 'author': 'Ivan Ferrer', 'author_id': 3455502, 'author_profile': 'https://Stackoverflow.com/users/3455502', 'pm_score': 4, 'selected': False, 'text': "<p>To count:</p>\n\n<pre><code>SELECT COUNT(*) as total FROM (SELECT TABLE_NAME as tab, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY tab) tables;\n</code></pre>\n\n<p>To list:</p>\n\n<pre><code>SELECT TABLE_NAME as table, TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='database_name' GROUP BY table;\n</code></pre>\n"}, {'answer_id': 46242383, 'author': 'Bridget Arrington', 'author_id': 355838, 'author_profile': 'https://Stackoverflow.com/users/355838', 'pm_score': 2, 'selected': False, 'text': '<p>You can create a stored procedure and put the table names in a cursor, then loop through your table names to show the data. </p>\n\n<p>Getting started with stored procedure:\n<a href="http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx" rel="nofollow noreferrer">http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx</a></p>\n\n<p>Creating a cursor:\n<a href="http://www.mysqltutorial.org/mysql-cursor/" rel="nofollow noreferrer">http://www.mysqltutorial.org/mysql-cursor/</a></p>\n\n<p>For example, </p>\n\n<pre><code>CREATE PROCEDURE `ShowFromTables`()\nBEGIN\n\nDECLARE v_finished INTEGER DEFAULT 0;\nDECLARE c_table varchar(100) DEFAULT "";\n\nDECLARE table_cursor CURSOR FOR \nSELECT table_name FROM information_schema.tables WHERE table_name like \'wp_1%\';\n\nDECLARE CONTINUE HANDLER \n    FOR NOT FOUND SET v_finished = 1;\n\nOPEN table_cursor;\n\nget_data: LOOP\n\nFETCH table_cursor INTO c_table;\n\nIF v_finished = 1 THEN \nLEAVE get_data;\nEND IF;\n\nSET @s=CONCAT("SELECT * FROM ",c_table,";");\n\nPREPARE stmt FROM @s;\nEXECUTE stmt;\nDEALLOCATE PREPARE stmt;\n\nEND LOOP get_data;\n\nCLOSE table_cursor;\n\nEND\n</code></pre>\n\n<p>Then call the stored procedure:</p>\n\n<pre><code>CALL ShowFromTables();\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/64894', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/-1/']