Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Sometimes I use SQLite database but it's quite common that I don't know anything about its structure.
I need to retrieve all metadata like the name of a database or table, the data type of a column, primary keys, foreign keys, or access privileges.
MSSQL has information_schema for this purpose but is there something similar for SQLite?
The equivalent of, for instance, MySQL describe t1; is
PRAGMA TABLE_INFO(t1);
and
SELECT * FROM sqlite_master WHERE tbl_name = 't1';
is also useful. Most of the time, however, I simply use
.schema t1
which outputs the SQL definition of the table or view, even with the original comments (exactly the same as SELECT sql FROM sqlite_master WHERE..., with less typing. .sch works too.)
SELECT * FROM sqlite_master ;