How to read metadata from Sqlite database

Question

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?

• joanolo: Check SQLite Pragmas, and SELECT * FROM sqlite_master ;

Answer 1

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.)