When we have a table with a generated column, here are two methods we can use to return its definition.

The .schema Command

We can use the .schema command to return the SQL used to create the table:

.schema Products

Result:

CREATE TABLE Products( 
    Id INTEGER PRIMARY KEY, 
    Name TEXT,
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price)
);

This method allows us to generate the SQL required to recreate the table. Therefore, it includes the definition of any generated columns in the table.

The sqlite_schema Table

We can alternatively use the sqlite_schema table to the same thing:

SELECT sql 
FROM sqlite_schema 
WHERE tbl_name = 'Products';

Result:

CREATE TABLE Products(                              
    Id INTEGER PRIMARY KEY,                         
    Name TEXT,                                      
    Qty INT,                                        
    Price INT,                                      
    TotalValue INT GENERATED ALWAYS AS (Qty * Price)
)      

The sqlite_schema table can also be accessed using sqlite_master (the sqlite_master table was renamed to sqlite_schema in SQLite 3.33.0, but it can still be accessed as sqlite_master for backwards compatibility).

The .dump Command

If you want the table data as well as the definition, use the .dump command. This command scripts the CREATE TABLE statement as well as the INSERT statements for inserting all data into the table.

Example:

.dump Products

Result:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Products( 
    Id INTEGER PRIMARY KEY, 
    Name TEXT,
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price)
);
INSERT INTO Products VALUES(1,'Hammer',10,9.9900000000000002131);
INSERT INTO Products VALUES(2,'Saw',5,11.339999999999998969);
INSERT INTO Products VALUES(3,'Wrench',7,37);
INSERT INTO Products VALUES(4,'Chisel',9,23);
INSERT INTO Products VALUES(5,'Bandage',70,120);
COMMIT;

However, this doesn’t include the data for the generated column. This is true whether the generated column is virtual or stored.

To test this, I’ve got another table called Products2 that has a stored generated column. Here’s what happens when I run the command against that table:

.dump Products2

Result:

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE Products2( 
    Id INTEGER PRIMARY KEY, 
    Name TEXT,
    Qty INT,
    Price INT,
    TotalValue INT GENERATED ALWAYS AS (Qty * Price) STORED
);
INSERT INTO Products2 VALUES(1,'Hammer',10,9.9900000000000002131);
INSERT INTO Products2 VALUES(2,'Saw',5,11.339999999999998969);
INSERT INTO Products2 VALUES(3,'Wrench',7,37);
INSERT INTO Products2 VALUES(4,'Chisel',9,23);
INSERT INTO Products2 VALUES(5,'Bandage',70,120);
COMMIT;

Same result.