List all columns in a SQLite database

Here's a devious trick for listing ALL columns in a SQLite database, using a SQL query that generates another SQL query.

The first query (demo):

select group_concat(
  "select '" || name || "' as table_name, * from pragma_table_info('" || name || "')"
, ' union ') || ' order by table_name, cid'
  from sqlite_master where type = 'table';

This outputs the second query, which will look something like this (demo):

select 'simple_primary_key' as table_name, * from pragma_table_info('simple_primary_key') union
select 'primary_key_multiple_columns' as table_name, * from pragma_table_info('primary_key_multiple_columns') union
select 'primary_key_multiple_columns_explicit_label' as table_name, * from pragma_table_info('primary_key_multiple_columns_explicit_label') union
select 'compound_primary_key' as table_name, * from pragma_table_info('compound_primary_key') union
select 'compound_three_primary_keys' as table_name, * from pragma_table_info('compound_three_primary_keys') union
select 'foreign_key_references' as table_name, * from pragma_table_info('foreign_key_references') union
select 'sortable' as table_name, * from pragma_table_info('sortable') union
select 'no_primary_key' as table_name, * from pragma_table_info('no_primary_key') union
select '123_starts_with_digits' as table_name, * from pragma_table_info('123_starts_with_digits') union
select 'Table With Space In Name' as table_name, * from pragma_table_info('Table With Space In Name') union
select 'table/with/slashes.csv' as table_name, * from pragma_table_info('table/with/slashes.csv') union
select 'complex_foreign_keys' as table_name, * from pragma_table_info('complex_foreign_keys') union
select 'custom_foreign_key_label' as table_name, * from pragma_table_info('custom_foreign_key_label') union
select 'units' as table_name, * from pragma_table_info('units') union
select 'tags' as table_name, * from pragma_table_info('tags') union
select 'searchable' as table_name, * from pragma_table_info('searchable') union
select 'searchable_tags' as table_name, * from pragma_table_info('searchable_tags') union
select 'searchable_fts' as table_name, * from pragma_table_info('searchable_fts') union
select 'searchable_fts_content' as table_name, * from pragma_table_info('searchable_fts_content') union
select 'searchable_fts_segments' as table_name, * from pragma_table_info('searchable_fts_segments') union
select 'searchable_fts_segdir' as table_name, * from pragma_table_info('searchable_fts_segdir') union
select 'select' as table_name, * from pragma_table_info('select') union
select 'infinity' as table_name, * from pragma_table_info('infinity') union
select 'facet_cities' as table_name, * from pragma_table_info('facet_cities') union
select 'facetable' as table_name, * from pragma_table_info('facetable') union
select 'binary_data' as table_name, * from pragma_table_info('binary_data') union
select 'roadside_attractions' as table_name, * from pragma_table_info('roadside_attractions') union
select 'attraction_characteristic' as table_name, * from pragma_table_info('attraction_characteristic') union
select 'roadside_attraction_characteristics' as table_name, * from pragma_table_info('roadside_attraction_characteristics')
order by table_name, cid

Executing that second query will return results like this:

table_name cid name type notnull dflt_value pk
123_starts_with_digits 0 content text 0 0
Table With Space In Name 0 pk varchar(30) 0 1
Table With Space In Name 1 content text 0 0
attraction_characteristic 0 pk integer 0 1
attraction_characteristic 1 name text 0 0
binary_data 0 data BLOB 0 0
complex_foreign_keys 0 pk varchar(30) 0 1
complex_foreign_keys 1 f1 text 0 0
complex_foreign_keys 2 f2 text 0 0
complex_foreign_keys 3 f3 text 0 0
compound_primary_key 0 pk1 varchar(30) 0 1
compound_primary_key 1 pk2 varchar(30) 0 2
compound_primary_key 2 content text 0 0
compound_three_primary_keys 0 pk1 varchar(30) 0 1
compound_three_primary_keys 1 pk2 varchar(30) 0 2
compound_three_primary_keys 2 pk3 varchar(30) 0 3
compound_three_primary_keys 3 content text 0 0
custom_foreign_key_label 0 pk varchar(30) 0 1
custom_foreign_key_label 1 foreign_key_with_custom_label text 0 0
facet_cities 0 id integer 0 1
facet_cities 1 name text 0 0
facetable 0 pk integer 0 1
facetable 1 created text 0 0
facetable 2 planet_int integer 0 0
facetable 3 on_earth integer 0 0
facetable 4 state text 0 0
facetable 5 city_id integer 0 0
facetable 6 neighborhood text 0 0
facetable 7 tags text 0 0
facetable 8 complex_array text 0 0
facetable 9 distinct_some_null 0 0
foreign_key_references 0 pk varchar(30) 0 1
foreign_key_references 1 foreign_key_with_label varchar(30) 0 0
foreign_key_references 2 foreign_key_with_no_label varchar(30) 0 0
infinity 0 value REAL 0 0
no_primary_key 0 content text 0 0
no_primary_key 1 a text 0 0
no_primary_key 2 b text 0 0
no_primary_key 3 c text 0 0
primary_key_multiple_columns 0 id varchar(30) 0 1
primary_key_multiple_columns 1 content text 0 0
primary_key_multiple_columns 2 content2 text 0 0
primary_key_multiple_columns_explicit_label 0 id varchar(30) 0 1
primary_key_multiple_columns_explicit_label 1 content text 0 0
primary_key_multiple_columns_explicit_label 2 content2 text 0 0
roadside_attraction_characteristics 0 attraction_id INTEGER 0 0
roadside_attraction_characteristics 1 characteristic_id INTEGER 0 0
roadside_attractions 0 pk integer 0 1
roadside_attractions 1 name text 0 0
roadside_attractions 2 address text 0 0
roadside_attractions 3 latitude real 0 0
roadside_attractions 4 longitude real 0 0
searchable 0 pk integer 0 1
searchable 1 text1 text 0 0
searchable 2 text2 text 0 0
searchable 3 name with . and spaces text 0 0
searchable_fts 0 text1 0 0
searchable_fts 1 text2 0 0
searchable_fts 2 name with . and spaces 0 0
searchable_fts 3 content 0 0
searchable_fts_content 0 docid INTEGER 0 1
searchable_fts_content 1 c0text1 0 0
searchable_fts_content 2 c1text2 0 0
searchable_fts_content 3 c2name with . and spaces 0 0
searchable_fts_content 4 c3content 0 0
searchable_fts_segdir 0 level INTEGER 0 1
searchable_fts_segdir 1 idx INTEGER 0 2
searchable_fts_segdir 2 start_block INTEGER 0 0
searchable_fts_segdir 3 leaves_end_block INTEGER 0 0
searchable_fts_segdir 4 end_block INTEGER 0 0
searchable_fts_segdir 5 root BLOB 0 0
searchable_fts_segments 0 blockid INTEGER 0 1
searchable_fts_segments 1 block BLOB 0 0
searchable_tags 0 searchable_id integer 0 1
searchable_tags 1 tag text 0 2
select 0 group text 0 0
select 1 having text 0 0
select 2 and text 0 0
select 3 json text 0 0
simple_primary_key 0 id varchar(30) 0 1
simple_primary_key 1 content text 0 0
sortable 0 pk1 varchar(30) 0 1
sortable 1 pk2 varchar(30) 0 2
sortable 2 content text 0 0
sortable 3 sortable integer 0 0
sortable 4 sortable_with_nulls real 0 0
sortable 5 sortable_with_nulls_2 real 0 0
sortable 6 text text 0 0
table/with/slashes.csv 0 pk varchar(30) 0 1
table/with/slashes.csv 1 content text 0 0
tags 0 tag TEXT 0 1
units 0 pk integer 0 1
units 1 distance int 0 0
units 2 frequency int 0 0

(I generated that Markdown table by pasting the HTML from Datasette into this tool: https://jmalarcon.github.io/markdowntables/)

Better alternative using a join

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

Demo.

This works with the pragma_table_info and pragma_index_list and pragma_foreign_key_list functions too.

Another recipe

Amjith pointed to this query used in litecli:

SELECT
  m.name as tableName,
  p.name as columnName
FROM
  sqlite_master m
  LEFT OUTER JOIN pragma_table_info((m.name)) p ON m.name <> p.name
WHERE
  m.type IN ('table', 'view')
  AND m.name NOT LIKE 'sqlite_%'
ORDER BY
  tableName,
  columnName

Demo.

Created 2020-05-06T08:58:14-07:00, updated 2020-12-17T17:45:24-08:00 · History · Edit