Display All Data of All Tables

Display all data of all tables

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

How do I get list of all tables in a database using TSQL?

SQL Server 2000, 2005, 2008, 2012, 2014, 2016, 2017 or 2019:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'

To show only tables from a particular database

SELECT TABLE_NAME 
FROM [<DATABASE_NAME>].INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

Or,

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' 
    AND TABLE_CATALOG='dbName' --(for MySql, use: TABLE_SCHEMA='dbName' )

PS: For SQL Server 2000:

SELECT * FROM sysobjects WHERE xtype='U' 

How do I display all tables in a database as HTML tables?

If you want to loop through all your tables and list the data from each one in a HTML table, including showing the column names, then this should work for you:

//get all the tables in the database
$sql = "SHOW TABLES FROM db_name";
$result = $connection->query($sql);

if ($result === false) die($conn->error);

//loop through the list of tables
while ($row = $result->fetch_row()) {
    echo "<h2>Table: ".$row[0]."</h2>";

    //now, for the current table, get all the data and loop through the rows
    $sql2 = "SELECT * FROM ".$row[0];
    $result2 = $connection->query($sql2);

    if ($result2)
    {
        //get the column names
        $fields = $result2->fetch_fields();
        echo "<table><thead><tr>";

        //loop through the field names and output each one as a column heading
        foreach ($fields as $fld)
        {
          echo "<th>".$fld->name."</th>";
        }
        echo "</tr></thead><tbody>";

        //get the rows
        while ($row2 = $result2->fetch_row()) {
            echo "<tr>";

            //loop through each data value in the row and output into a HTML table cell
            foreach ($row2 as $cell) {
                echo "<td>".$cell."</td>";
            }
            echo "</tr>";
        }

        echo "</tbody></table><hr>";
    }
    else
    {
      echo "Problem retrieving data for ".$row[0].": ".$conn->error;
    }
}

Select all data from all tables in a database and print into one table

Using the query like you shown won't lead to the desired result:

SELECT * FROM table1, table2, table3...

Will return a Cartesian product of all the columns in all the tables. If you want just a sequence of rows from all the tables, you need is a UNION:

(SELECT * FROM table1)
UNION
(SELECT * FROM table2)
UNION
(SELECT * FROM table3)
...
ORDER BY date DESC;

Get size of all tables in database

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    TotalSpaceMB DESC, t.Name

Query to display all tables and views

I'd prefer the xxx_objects views myself for this purpose (as Justin says), but if you specifically need other data from the table and view views, you can add extra info thus:

select 'Table' AS object_type, table_name
from all_tables
where owner = '<owner>'
UNION ALL
select 'View' AS object_type, view_name
from all_views
where owner = '<owner>'
order by table_name;

Note I've changed it to use UNION ALL because there will be no collisions between the two result sets.

---

Related Topics