issue_comments: 1317465874

I was worrying about the server-side overhead of collecting together all of the tables and column names for databases that might have hundreds of tables... but then I remember that I built the _internal table precisely for this kind of thing - so gathering all of that data should still only be a single SQL query against an in-memory database.

https://latest.datasette.io/login-as-root and then visit this page for an example query: https://latest.datasette.io/_internal?sql=select%0D%0A++database_name%2C%0D%0A++table_name%2C%0D%0A++json_group_array%28name%29%0D%0Afrom%0D%0A++columns%0D%0Awhere%0D%0A++database_name+%21%3D+%27_internal%27%0D%0Agroup+by%0D%0A++database_name%2C%0D%0A++table_name

sql select database_name, table_name, json_group_array(name) from columns where database_name != '_internal' group by database_name, table_name

database_name | table_name | json_group_array(name) -- | -- | -- extra_database | searchable | ["pk","text1","text2"] extra_database | searchable_fts | ["__langid","content","docid","searchable_fts","text1","text2"] extra_database | searchable_fts_content | ["c0text1","c1text2","c2content","docid"] extra_database | searchable_fts_segdir | ["end_block","idx","leaves_end_block","level","root","start_block"] extra_database | searchable_fts_segments | ["block","blockid"] fixtures | 123_starts_with_digits | ["content"] fixtures | Table With Space In Name | ["content","pk"] fixtures | attraction_characteristic | ["name","pk"] fixtures | binary_data | ["data"] fixtures | complex_foreign_keys | ["f1","f2","f3","pk"] fixtures | compound_primary_key | ["content","pk1","pk2"]