SQLite: sqlite_master (internal schema object)

The structure of sqlite_master can be displayed with the SQLite shell command .schema sqlite_master:

The value of tbl_name is particularly interesting if the record refers to an index (that is type = 'index'). In that case, tbl_name tells on which table the index is created.

Using sqlite_master

The following simple SQL script tries to demonstrate how sqlite_master might be used to query for information about schema objects:

create table foo (
  id,
  col_1,
  col_2  text,
  col_3  check (col_3 > 5 and col_3 < 10),
  --
  constraint foo_pk primary key (id),
  constraint foo_uq unique(col_2, col_3)
);

create index foo_ix on foo (col_2);

create view foo_v as
select
  col_1,
  col_3,
  col_1 + col_2 sum_col
from
  foo;


select type, name from sqlite_master where tbl_name = 'foo';
--
-- table|foo
-- index|sqlite_autoindex_foo_1
-- index|sqlite_autoindex_foo_2
-- index|foo_ix


select type, name from sqlite_master where tbl_name = 'foo_v';
--
-- view|foo_v


select sql from sqlite_master where name = 'foo';
--
-- CREATE TABLE foo (
--   id,
--   col_1,
--   col_2  text,
--   col_3  check (col_3 > 5 and col_3 < 10),
--   --
--   constraint foo_pk primary key (id),
--   constraint foo_uq unique(col_2, col_3)
-- )

  

select name, type from sqlite_master where sql is not null;
-- 
-- foo|table
-- foo_ix|index
-- foo_v|view

drop table foo;

Github repository about-sqlite, path: /internal-schema-objects/sqlite_master/example.sql

analyze sqlite_master

When an application changes the statistics tables directly, SQLite will not immediately notice the changes.

An application can then force the query planner to re-read the statistics table by running

analyze sqlite_master;

SQLite: sqlite_master (internal schema object)

Using sqlite_master

analyze sqlite_master

See also