SQLite Describe a Table

1. SQLite Describe a Table
2. The Schema Table

SQLite is a serverless open-source SQL database that stores data on a text file on any device. It is a reliable, fast, self-contained, full-featured SQL database engine in the C programming library.

This software is quite popular throughout the world. It has built-in mobile phones, most computers, and various applications worldwide.

SQLite comes equipped with several different commands for its users. One of these commands is the describe command.

This allows the user to see a detailed structure of the table.

SQLite Describe a Table

SQLite is a relational database management system that employs many commands at the user’s disposal. This database software uses the .schema command to describe a table in the database.

Here, the .schema command is a command line program that we can use to get a detailed structure of the required table. Describing the tables essentially means that we can print specific details of all the columns in the table, such as column name, the data type of the column as well as the size of the column.

SQLite allows the description of the table in different ways.

Describe a Table Using .schema in SQLite

The syntax to describe a table is the following:

.schema yourTableName

Here yourTableName is the table name whose description you require, and .schema is the command you need to use. To see all tables, use the command .tables; however, if you need to see a particular table, use the command .schema tablename.

If the .schema or query from sqlite_master does not give any output, this indicates a non-existent table name.

Describe a Table Using PRAGMA in SQLite

As we mentioned before, the .schema method can be used to describe the table with the specified table name. However, in this command, we need to create the table first.

The next method we can use is the PRAGMA method. The syntax for using this command is the following:

PRAGMA table_info(tablename)

This command is the same as the SQL describe command. It returns a single row from every column of the table you have specified in the column.

PRAGMA table_xinfo(tablename)

This is another version of the previous statement. The difference here is that it returns the hidden column of the virtual table specified in the statement.

.schema, in this case, can show more details of the tables, including table constraints, than PRAGMA.

If you need the details of all tables in a well-formatted way, use the following command.

.schema --indent

Describe a Table Using sqlite_master in SQLite

Another command is:

sqlite_master tablename

This is a command that is used to describe a table.

The Schema Table

Every SQLite database consists of a solitary schema table. This table holds the database schema, which depicts everything in the tables, from lists and triggers to views inside the database.

The schema table looks like this:

 CREATE TABLE sqlite_schema(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  SQL text
);

SQLite creates the schema table when the database is created and continues to modify its content as the SQLite users submit statements for execution.

Under normal conditions, there is no need to modify it. If users do modify it, they have to bear the risk of database corruption.

The sqlite_schema contains a single row for each table, index, view, and trigger in the schemas except when there is no entry for the sqlite_schema table itself. The sqlite_schema is a reference for the schema table and has some alternative names such as sqlite_master, sqlite_temp_schema, and sqlite_temp_master.

Here the last two alternatives are used for the temp database. They are associated with every database connection.

When we talk about the Schema Table, the schema uses different parameters such as type, name, tbl_name, root page, and SQL.

type means text string such as view, index, table, or trigger since it depends on which type of object is defined. We can represent the type as sqlite_schema.type. name specifies the column store name of the object.

Here, we can also specify unique and primary key constraints at the table creation time. However, we can’t use the primary key constraint with the rowid table, so sqlite_schema does not allow primary, but SQLite auto index uses the primary key.

tbl_name is used to store the table name or the view name. We can represent tbl_name as sqlite_schema_tbl_name.

The root page is used to store the page number of the root tree page for the table and index. We represent it as sqlite_schema_rootpage.

SQL provides different commands. This means we can create triggers that can be executed against the database.

We represent SQL as sqlite_schem.sql. The text in the sqlite_schema.sql column is a copy of the original CREATE statement text that was created; however, it normalized as described before and as modified by proceeding ALTER TABLE statements.

It is NULL for the internal indexes; these are automatically created by PRIMARY KEY or UNIQUE constraints.

Example of Describing Tables in SQLite

First, create a table by using the create table statement.

create table employee (id integer primary key, name text not null, dept text not null, salary text not null);

Now enter the command:

PRAGMA table_info(employee);

As mentioned before, this command describes the table.

.schema employee

This will also do the same:

SELECT SQL
FROM sqlite_master
WHERE tbl_name = 'employee';

Here we used a select statement with sql_master to extract the SQL parameter of the sqlite_master command.