Searching online, it's pretty easy to find the way to get information on all columns in a SQLite table: use the pragma table_info(name) with the name of the table. But every SQL database I've seen has some way to get this info for all columns for the entire database at once, with some column in the result set linking back to the table that owns the column in question.

Does SQLite support this? Or can it only be done by making a separate query for each individual table?

CodePudding user response：

You can query the table sqlite_master to get all the tables of the database and then use the table valued function pragma_table_info() to get for each table its columns:

WITH all_tables AS (SELECT name FROM sqlite_master WHERE type = 'table') 
SELECT at.name table_name, pti.*
FROM all_tables at INNER JOIN pragma_table_info(at.name) pti
ORDER BY table_name;