Way of seeing full schema for a database

[simonw]

I find myself wanting to quickly figure out all of the BLOB columns in a database.

A /-/schema page showing the full schema (actually since it's per-database probably /dbname/-/schema or /-/schema/dbname) would be really handy.

It would need to be carefully constructed from various queries against sqlite_master - just doing select * from sqlite_master where type='table' isn't quite enough because I also want to show indexes, triggers etc.

[simonw]

select * from pragma_table_info(tablename); is currently disallowed for user-provided queries via a regex restriction - but could help here too.

datasette/datasette/utils/__init__.py

Line 174 in d349d57

disallawed_sql_res = [(re.compile("pragma"), "Statement may not contain PRAGMA")]

[simonw]

Potential recipe in https://github.com/simonw/til/blob/master/sqlite/list-all-columns-in-a-database.md

[simonw]

select
  sqlite_master.name as table_name,
  table_info.*
from
  sqlite_master
  join pragma_table_info(sqlite_master.name) as table_info
order by
  sqlite_master.name,
  table_info.cid

https://latest.datasette.io/fixtures?sql=select%0D%0A++sqlite_master.name+as+table_name%2C%0D%0A++table_info.*%0D%0Afrom%0D%0A++sqlite_master%0D%0A++join+pragma_table_info%28sqlite_master.name%29+as+table_info%0D%0Aorder+by%0D%0A++sqlite_master.name%2C%0D%0A++table_info.cid