Display all data of all tables

I want to display all data in my Database without writing a select for each table - how can I do this?

I do not want to do this:

select * from Customer
select * from Employee
select .............

I am using TSQL with MSSQL Server.

回答1:

DECLARE @sqlText VARCHAR(MAX)
SET @sqlText = ''
SELECT @sqlText = @sqlText + ' SELECT * FROM ' + QUOTENAME(name) + CHAR(13) FROM sys.tables
EXEC(@sqlText)

回答2:

For 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.

回答3:

Use database admin tools to dump the database without schema.

回答4:

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, phpMyAdmin is perfect for the job - a tool that I can almost guarantee most SQL guys are appreciative of.

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
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!