SQLite describe table

In SQLite, the DESCRIBE TABLE statement is not directly used. Instead, you typically use the PRAGMA table_info(table_name) statement to obtain information about the columns in a table. This statement provides details about the columns, such as the column name, data type, whether it can contain NULL values, and a few other attributes.


Here’s the syntax of how you can use the PRAGMA table_info statement to describe a table in SQLite:

PRAGMA table_info(your_table_name);

This statement returns a result set with the following columns:

cid: The column ID.
name: The name of the column.
type: The data type of the column.
notnull: A flag indicating whether the column can contain NULL values (1 for true, 0 for false).
dflt_value: The default value for the column.
pk: A flag indicating whether the column is part of the primary key (1 for true, 0 for false).


Here’s a simple example:

PRAGMA table_info(employees);

The result might look like this:

| cid | name        | type      | notnull | dflt_value | pk |
| 1   | employee_id | INTEGER   | 1       | NULL       | 1  |
| 2   | first_name  | TEXT      | 0       | NULL       | 0  |
| 3   | last_name   | TEXT      | 0       | NULL       | 0  |
| 4   | salary      | REAL      | 0       | NULL       | 0  |

In this example, the employees table has four columns (employee_id, first_name, last_name, and salary). The employee_id column is the primary key, indicated by the value 1 in the pk column.

By examining the output of the PRAGMA table_info statement, you can gain insights into the structure of the table, including the data types of each column and whether they allow NULL values. This information is useful for understanding and working with the schema of your SQLite database.

Keep in mind that SQLite uses the term PRAGMA for various implementation-specific commands and settings, and PRAGMA table_info is specifically used for retrieving information about table columns.