How to Check Which Tables Contain Data in a Database

In database management, it is important to quickly identify the table of data containing data, which helps to check problems, maintain the database or understand the database structure and usage. This article describes whether there is data in the check form in different relationship database management systems (RDBMS).

Postgresql

PostgreSQL uses

dynamic generating query to check the number of all tables in the architecture:

pg_catalog.pg_tables Use PL/PGSQL block:

This script traverses all the tables in the architecture and outputs the number of rows of each table.

<code class="language-sql">DO $$
DECLARE
    tbl RECORD;
BEGIN
    FOR tbl IN
        SELECT schemaname, tablename
        FROM pg_catalog.pg_tables
        WHERE schemaname = 'public'  --  根据需要更改架构
    LOOP
        EXECUTE format(
            'SELECT COUNT(*) AS row_count, ''%I'' AS table_name FROM %I.%I',
            tbl.tablename, tbl.schemaname, tbl.tablename
        );
    END LOOP;
END $$;</code>

Alternative query: public

If you want to view the number of all tables in one query:

This method lists the table and its corresponding rows.

<code class="language-sql">SELECT table_name,
       (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public') AS row_count
FROM information_schema.tables
WHERE table_schema = 'public';</code>

mysql

MySQL checks the data of each table by directly querying the number of rows of each table. The following is a dynamic method of performing this operation: Get the query of the number of rows:

replaced with your database name.

<code class="language-sql">SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'your_database_name';</code>

The number of approximation of each table is provided (note that this value may not always be 100% accurate).

• your_database_name SQL Server
• table_rows SQL Server uses
System view dynamically check all the number of rows of all tables:

Query: This query returns the number of all user tables in the current database.

sqlite sys.tables

SQLite uses the

table to obtain the table and dynamically query the number of each table:

<code class="language-sql">SELECT t.name AS table_name,
       p.rows AS row_count
FROM sys.tables t
JOIN sys.partitions p
     ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1);  -- 0 = 堆，1 = 聚集索引</code>

Query:

This query lists all tables and their rows. Summary

sqlite_master Checking which tables contain data are a common task in all databases. Most databases provide methods to dynamically generate query or use system views to calculate the number of rows in each table. Using these technologies, you can quickly identify which tables include data, and more effectively understand the structure of the database.

The above is the detailed content of How to Check Which Tables Contain Data in a Database. For more information, please follow other related articles on the PHP Chinese website!