SQL 显示所有表的所有数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7106108/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Display all data of all tables
提问by Salah Sanjabian
I want to display all data in my Database without writing a selectfor each table - how can I do this?
我想显示我的数据库中的所有数据而不select为每个表编写一个- 我该怎么做？
I do not want to do this:
我不想这样做：
select * from Customer
select * from Employee
select .............
I am using TSQL with MSSQL Server.
我正在将 TSQL 与 MSSQL Server 一起使用。
回答by Vishal Gajjar
DECLARE @sqlText VARCHAR(MAX)
SET @sqlText = ''
SELECT @sqlText = @sqlText + ' SELECT * FROM ' + QUOTENAME(name) + CHAR(13) FROM sys.tables
EXEC(@sqlText)
回答by Andrej Ludinovskov
For mysql:
对于 mysql：
Run SELECT information_schema.TABLES.TABLE_NAME FROM information_schema.TABLES where table_schema='db_name'
Create a loop which will run select query for each table gotten from the first query.
运行 SELECT information_schema.TABLES.TABLE_NAME FROM information_schema.TABLES where table_schema='db_name'
创建一个循环，该循环将为从第一个查询获得的每个表运行选择查询。
回答by Bohemian
Use database admin tools to dump the database without schema.
使用数据库管理工具转储没有架构的数据库。
回答by Bohemian
Your question feels rather limited to me. What language are you using? What is the purpose of this? For that reason, this is a multipart answer.
你的问题对我来说感觉相当有限。你使用什么语言？这样做的目的是什么？出于这个原因，这是一个多部分的答案。
First of all, for seeing all the data in a MySql database, phpMyAdminis perfect for the job - a tool that I can almost guarantee most SQL guys are appreciative of.
首先，为了查看 MySql 数据库中的所有数据，phpMyAdmin非常适合这项工作 - 我几乎可以保证大多数 SQL 人员都喜欢这个工具。
For the second part of the question: I'm presuming you want to do this dynamically, or there's just too many tables to write a query for each time. I'll presume the dynamically one, because I have created databases in the past which automatically adds tables to itself as it grows. This solution in PHP is one I've just written for you which should display everything in a table. EDIT: This code is bugged - all the data is displayed but I've gotten the table formatting wrong.- EDIT: fixed.
对于问题的第二部分：我假设您想动态执行此操作，或者每次都需要为太多表编写查询。我会假设是动态的，因为我过去创建了数据库，它会随着表的增长自动添加表。这个 PHP 解决方案是我刚刚为您编写的解决方案，它应该在表格中显示所有内容。 编辑：这段代码被窃听了——所有的数据都显示出来了，但我把表格格式弄错了。-编辑：固定。
<?php
listAll("table_name");
function listAll($db) {
mysql_connect("localhost","root","");
mysql_select_db($db);
$tables = mysql_query("SHOW TABLES FROM $db");
while (list($tableName)=mysql_fetch_array($tables)) {
$result = mysql_query("DESCRIBE $tableName");
$rows = array();
while (list($row)=mysql_fetch_array($result)) {
$rows[] = $row;
}
$count = count($rows);
if ($count>0) {
echo '<p><strong>',htmlentities($tableName),'</strong><br /><table border="1"><tr>';
foreach ($rows as &$value) {
echo '<td><strong>',htmlentities($value),'</strong></td>';
}
echo '</tr>';
$result = mysql_query("SELECT * FROM $tableName");
while ($row=mysql_fetch_array($result)) {
echo '<tr>';
for ($i=0;$i<(count($row)/2);$i++) {
echo '<td>',htmlentities($row[$i]),'</td>';
}
echo '</tr>';
}
echo '</table></p>';
}
}
return FALSE;
}
?>
And hopefully that code snippet should work for you like it's worked for me!
希望该代码片段对您有用，就像对我有用一样！
回答by Tarek
adding table name header to the script of Vishal Gajjar:
将表名标题添加到 Vishal Gajjar 的脚本中：
DECLARE @sqlText VARCHAR(MAX)
SET @sqlText = ''
SELECT @sqlText = @sqlText + 'select '''+QUOTENAME(name)+'''; SELECT * FROM ' + QUOTENAME(name) + CHAR(13) FROM sys.tables
EXEC(@sqlText)
