I received this question from a student:

I am taking your SQL Essential Training course. Can you please tell me how I can find the table and column names in the database, like .tables or DESCRIBE in different system?

For my SQL Essential Training title [lynda] [LiL], I chose to use the SQLite database engine. I made this choice because it’s a full-featured and fully compliant SQL database engine, it’s small and fast, and as a self-contained database system it doesn’t require installation and configuration of a server and client.

Every database engine has its own protocols for inspecting its internal meta-data. How many tables in this database? What are the column definitions for this table? What indexes are available in this database? SQLite uses a set of meta-tables for this purpose.

The sqlite_master table will have most of the information you need. Here you will find table and column definitions, indexes, triggers, and other information related to the database.

SQLite is designed to be as simple and useable as possible. Putting all this relevant information in one table is both effective and useful.