SQLite Table Schema Inspection: Beyond MySQL's DESCRIBE Command

Introduction to Table Schema Inspection in SQLite

When transitioning from MySQL to SQLite, database administrators and developers often seek equivalent functionality for examining table structures. While MySQL provides the convenient DESCRIBE [table] command, SQLite offers several alternative approaches that serve similar purposes with distinct implementations and capabilities.

Primary Method: SQLite Command Line .schema Command

The most direct equivalent to MySQL's DESCRIBE in SQLite is the .schema [TABLENAME] command available in the SQLite command-line utility. This command displays the complete CREATE TABLE statement used to generate the specified table, providing comprehensive structural information including column definitions, data types, constraints, and default values.

For example, consider a table created with:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    salary REAL DEFAULT 0.0,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Executing .schema employees in the SQLite CLI would output the exact CREATE TABLE statement, revealing the complete table structure including primary key constraints, foreign key relationships, and default values.

Alternative Approach: PRAGMA table_info

SQLite provides the PRAGMA table_info([tablename]) statement as another method for examining table structure. This pragma returns a result set containing basic column information organized in a tabular format.

The returned columns include:

• cid: Column ID number
• name: Column name
• type: Data type declaration
• notnull: Boolean indicating if NULL values are allowed (0 or 1)
• dflt_value: Default value for the column
• pk: Boolean indicating if the column is part of the primary key (0 or 1)

While PRAGMA table_info provides structured data that can be easily processed programmatically, it lacks some detailed information available in the complete CREATE TABLE statement returned by the .schema command.

Advanced Method: Querying sqlite_schema Table

For programmatic access to table definitions, SQLite maintains the sqlite_schema system table (historically known as sqlite_master). This table contains the original SQL statements used to create database objects.

The basic query structure is:

SELECT sql FROM sqlite_schema WHERE type = 'table' AND name = 'table_name';

This approach is particularly useful when working with SQLite programmatically through various programming language interfaces, as it allows direct retrieval of the complete table definition SQL.

Practical Implementation Examples

Let's examine a practical scenario where we need to inspect a complex table structure. Consider a products table with various constraints:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_name TEXT NOT NULL CHECK(length(product_name) > 0),
    category TEXT NOT NULL,
    price DECIMAL(10,2) CHECK(price >= 0),
    in_stock INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(product_name, category)
);

Using .schema products would display the complete table definition, including all constraints and default values. The PRAGMA table_info(products) would show individual column properties, while querying sqlite_schema would retrieve the original SQL statement for programmatic use.

Comparison and Best Practices

Each method serves different use cases:

• .schema command: Best for interactive use in CLI, provides complete human-readable table definition
• PRAGMA table_info: Ideal for programmatic analysis of column properties and metadata
• sqlite_schema query: Most flexible for applications requiring the original SQL definition

For most development and administrative tasks, the .schema command provides the most comprehensive and readable output, closely matching the functionality of MySQL's DESCRIBE command while leveraging SQLite's unique architecture.