Using SQLite

System Catalogs

Many relational database systems, including SQLite, keep system state data in a series of data structures known as system catalogs. All of the SQLite system catalogs start with the prefix sqlite_. Although many of these catalogs contain internal data, they can be queried, using SELECT, just as if they were standard tables. Most system catalogs are read-only. If you encounter an unknown database and you’re not sure what’s in it, examining the system catalogs is a good place to start.

All nontemporary SQLite databases have an sqlite_master catalog. This is the master record of all database objects. If any of the tables has a populated AUTOINCREMENT column, the database will also have an sqlite_sequence catalog. This catalog is used to keep track of the next valid sequence value (for more information on AUTOINCREMENT, see Primary keys). If the SQL command ANALYZE has been used, it will also generate one or more sqlite_stat# tables, such as sqlite_stat1 and sqlite_stat2. These tables hold various statistics about the values and distributions in various indexes, and are used to help the query optimizer pick the more efficient query solution. For more information, see ANALYZE in Appendix C.

The most important of these system catalogs is the sqlite_master table. This catalog contains information on all the objects within a database, including the SQL used to define them. The sqlite_master table has five columns:

Column name	Column type	Meaning
type	Text	Type of database object
name	Text	Identifier name of object
tbl_name	Text	Name of associated table
rootpage	Integer	Internal use only
sql	Text	SQL used to define object

The type column can be table (including virtual tables), index, view, or trigger. The name column gives the name of the object itself, while the tbl_name column gives the name of the table or view the object is associated with. For tables and views, the tbl_name is just a copy of the name column. The final sql column holds a full copy of the original SQL command used to define the object, such as a CREATE TABLE or CREATE TRIGGER command.

Temporary databases do not have an sqlite_master system catalog. Rather, they have an sqlite_temp_master table instead.