How to use a Custom Query In SQL

1. Calculating SQLite Database Size Using a Custom Query

We will start by querying the sqlite_master table. Now in subquery:

We calculate the estimated number of pages (pgs) required for each table by considering the length of its SQL definition (sql) and converting it to an approximate number of pages.

Now, join the information obtained from the previous subquery with the pragma_page_count() pragma, which provides the total number of pages (page_count) in the database. Then calculate the size of each table (pgcnt) by multiplying the estimated page count (pgs) with the total page count (pgsz). Finally, obtaining the size of each table, we use the SUM function to sum up all the table sizes

Query:

SELECT 
    SUM(pgsize) AS "Total Size (Bytes)"
FROM (
    SELECT 
        name,
        (pgcnt * 4096) AS pgsize
    FROM (
        SELECT 
            name,
            (pgs * pgsz) AS pgcnt
        FROM (
            SELECT 
                name,
                ((1 + ((length(sql) + 1) / 1024)) / 1024) AS pgs
            FROM 
                sqlite_master
        ),
        (SELECT 
            page_count AS pgsz
        FROM 
            pragma_page_count())
    )
    WHERE 
        type='table'
);

Output:

Using a Custom Query

Explanation: The SQL query calculates the total size of all tables in the SQLite database. It estimates the number of pages needed for each table based on its SQL definition length. By multiplying this estimated page count with the total page count obtained from pragma_page_count(), it computes the size of each table. Finally, the SUM function aggregates all table sizes to determine the total database size in bytes.

2. Using a Custom Query to Calculate SQLite Database Size

Using the following formula, we can calculate the database size.

We will begin by querying the sqlite_master table, which contains metadata about all the tables in the database then exclude system tables by filtering out those whose names start with “sqlite_“. We will join the information obtained from sqlite_master, and finally calculate the size of each table by multiplying the number of pages.

After obtaining the size of each table, we will use the SUM function to sum up all the table sizes, resulting in the total size of the database.

SELECT 
    SUM(total_bytes) AS "Total Size (Bytes)"
FROM (
    SELECT 
        name,
        (page_count * page_size) AS total_bytes
    FROM 
        pragma_page_size
    JOIN 
        pragma_page_count ON 1 = 1
    JOIN 
        sqlite_master ON type = 'table'
    WHERE 
        name NOT LIKE 'sqlite_%'
);

Output:

How to Get SQLite Database Size using a custom query

Explanation: This SQL query computes the total size, in bytes, of user-defined tables in the SQLite database. It joins information from pragma_page_size and pragma_page_count with sqlite_master to calculate the total bytes occupied by each table. The SUM function then aggregates these values to provide the total database size in bytes, excluding system tables.

Note:

The PRAGMA statements in Example 1 provide information about the physical structure of the database, such as the total number of pages and the size of each page. These values are inherent to the database file itself and are not influenced by the data stored in the tables.
The alternative approach in Example 2 and 3, estimates the size of the database by analyzing the metadata of the tables and making assumptions about their size based on their SQL definitions. This approach may provide a different result because it’s based on estimations rather than direct measurements of the database file.

How to Get SQLite Database Size

SQLite is a lightweight and serverless SQL database. It is widely used in mobile devices and embedded systems due to its simplicity and efficiency. To understand how to manage SQLite databases efficiently, it is very important to know the Database size in SQLite. In this article, we will learn about different approaches to get the SQLite Database Size.

To retrieve the size of a SQLite database, you can use the PRAGMA statement along with the page_count and page_size properties.

Syntax: