Handed an undocumented SQLite database from a departed developer's application, dozens of tables with cryptic names. I need to find where things live, which tables carry an email column for instance.
What are the interrogation queries for SQLite's own structure?
SQLite keeps its whole structure queryable, so an undocumented database interrogates nicely. The toolkit from broad to narrow:
The inventory: SELECT name, type FROM sqlite_master WHERE type IN ('table','view') ORDER BY name lists everything, with the sql column of the same table holding each object's complete CREATE statement, the departed developer's schema in his own words. Reading a few CREATE statements often decodes the naming scheme faster than any cleverness.
The column hunt you asked for: sqlite_master's sql column is searchable text, SELECT name FROM sqlite_master WHERE type = 'table' AND sql LIKE '%email%' finds every table whose definition mentions email anywhere. For precision over the definition text, modern SQLite offers PRAGMA functions usable in queries: SELECT m.name AS table_name, p.name AS column_name FROM sqlite_master m JOIN pragma_table_info(m.name) p WHERE m.type = 'table' AND p.name LIKE '%email%' returns exact table and column pairs, the proper answer to which tables carry an email column. The same join without the LIKE dumps the entire column catalog for a spreadsheet.
The relationships and the sizes complete the picture: pragma_foreign_key_list(m.name) in the same join style maps which tables reference which, sketching the schema diagram nobody drew, while row counts per table, a quick loop or the DB Browser for SQLite Database Structure tab, show where the data mass lives. An hour with these four angles typically turns cryptic into merely unfamiliar.
The pragma_table_info join found email columns in four tables including one named usr_x2 I would never have guessed. Foreign key mapping sketched the whole design in twenty minutes. The departed developer's CREATE statements even had comments, buried treasure.