Introduction

SQLite does not implement MySQL's DESCRIBE table_name syntax directly. Instead, SQLite exposes schema details through PRAGMA commands, shell helpers such as .schema, and catalog tables like sqlite_master, which together cover the same use cases with slightly different tools.

The Closest Replacement: PRAGMA table_info

If what you want from DESCRIBE is a quick list of columns and their metadata, the direct SQLite equivalent is usually:

PRAGMA table_info(users);

This returns one row per column with fields such as:

• 'cid for the column index'
• 'name for the column name'
• 'type for the declared type'
• 'notnull indicating whether NOT NULL was declared'
• 'dflt_value for the default value'
• 'pk showing primary-key position'

That covers the most common “tell me about this table” workflow.

Example

1CREATE TABLE users (
2    id INTEGER PRIMARY KEY,
3    email TEXT NOT NULL,
4    created_at TEXT DEFAULT CURRENT_TIMESTAMP
5);
6
7PRAGMA table_info(users);

Typical results would tell you that id is part of the primary key, email is non-nullable, and created_at has a default value.

Compared with MySQL DESCRIBE, the information is similar in purpose even if the exact formatting differs.

When table_info Is Not Enough

PRAGMA table_info is intentionally compact. It does not show:

• indexes
• triggers
• foreign-key clauses in full text
• the complete original CREATE TABLE SQL

So if you need the whole schema definition rather than a column summary, use other SQLite introspection tools.

Seeing the Full Table Definition

In the sqlite3 shell, the most convenient command is:

.schema users

That prints the CREATE TABLE statement and often related objects such as indexes:

1CREATE TABLE users (
2    id INTEGER PRIMARY KEY,
3    email TEXT NOT NULL,
4    created_at TEXT DEFAULT CURRENT_TIMESTAMP
5);

This is often closer to what developers actually want when they say “describe the table,” because it shows the exact SQL structure rather than a reduced metadata view.

Querying sqlite_master

SQLite also stores schema definitions in a system catalog table called sqlite_master.

SELECT type, name, tbl_name, sql
FROM sqlite_master
WHERE tbl_name = 'users';

This is useful when you want to inspect not just the table, but also indexes, views, and triggers associated with it.

sqlite_master is especially handy in application code because it is queryable like ordinary SQL rather than being tied to the interactive shell.

PRAGMA table_xinfo for More Complete Column Metadata

For newer SQLite features such as hidden or generated columns, table_info can be incomplete. In that case, use:

PRAGMA table_xinfo(users);

This behaves like a fuller version of table_info. If you are working with virtual tables or generated columns and something seems to be missing, table_xinfo is often the missing piece.

Introspecting Indexes and Foreign Keys

A MySQL user coming from DESCRIBE often also wants related structural details. SQLite splits these into separate pragmas.

Indexes:

PRAGMA index_list(users);

Foreign keys:

PRAGMA foreign_key_list(users);

That modular approach is very SQLite-like. Instead of one catch-all convenience statement, you use small focused introspection commands depending on what you need.

Choosing the Right Tool

A practical rule of thumb is:

• use PRAGMA table_info for column summaries
• use PRAGMA table_xinfo when advanced column metadata matters
• use .schema in the shell for full object definitions
• use sqlite_master in SQL when you want raw schema records
• use PRAGMA index_list and PRAGMA foreign_key_list for related structural details

Once you know that toolkit, the lack of DESCRIBE stops being a problem.

Common Pitfalls

• Running DESCRIBE users; directly in SQLite and expecting MySQL syntax to work.
• Assuming PRAGMA table_info contains every detail about indexes, triggers, and foreign keys.
• Forgetting that .schema is a shell command, not standard SQL you can run through every database driver.
• Looking only at table_info when hidden or generated columns require table_xinfo instead.
• Treating SQLite schema inspection like a single-command workflow when it is really a small set of focused inspection tools.

Summary

• SQLite has no direct DESCRIBE statement, but PRAGMA table_info(table_name) is the closest equivalent.
• Use .schema when you want the full CREATE TABLE statement in the SQLite shell.
• Query sqlite_master when you want raw schema records from SQL.
• Use table_xinfo for richer column metadata in advanced cases.
• Think of SQLite schema inspection as a toolkit of PRAGMA commands rather than one MySQL-style convenience statement.