How can one see the structure of a table in SQLite?

The common way to view the structure of a table in SQLite is by using the PRAGMA statement. Specifically, you can use the command PRAGMA table_info(table_name); to inspect the structure of a specified table. This command returns detailed information about each column, including column name, data type, whether it can be NULL, and default value.

For example, suppose we have a table named students with three fields: id, name, and age. To view the structure of this table, input the following command in the SQLite command-line tool:

sql
PRAGMA table_info(students);

After execution, the output may appear as:

shell
cid | name | type    | notnull | dflt_value | pk
----|------|---------|---------|------------|---
0   | id   | INTEGER | 1       | None       | 1
1   | name | TEXT    | 0       | None       | 0
2   | age  | INTEGER | 0       | None       | 0

Here:

• cid is the column ID.
• name is the column name.
• type is the data type.
• notnull indicates whether the column can be NULL (1 means it cannot be NULL, 0 means it can be NULL).
• dflt_value is the default value for the column.
• pk indicates whether the column is a primary key (1 means it is, 0 means it is not).

Using PRAGMA table_info provides a quick and direct method to view table structure, aiding developers in understanding and manipulating the database.