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 d开发者_StackOverflowo this?

I do not want to do this:

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

I am using TSQL with MSSQL Server.

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

For mysql:

1. Run SELECT information_schema.TABLES.TABLE_NAME FROM information_schema.TABLES where table_schema='db_name'

2. Create a loop which will run select query for each table gotten from the first query.

Use database admin tools to dump the database without schema.

adding table name header to the script of 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)

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!