Describe all tables to text file

Question

How can one describe every table in a database and export all the results to a text file? E.g.

\o describe.txt
\d+ MY_TABLE
\o

but for every table, appending the output to the text file each time.

Answer 1

I think you might want:

\dt+ *.*

i.e "all tables in all schemas". Use \d instead of \dt if you want to include views too.

I can't help wondering ... why? If you're doing this for documentation check out SchemaSpy as a much nicer alternative.

Answer 2

To get output by SQL query, please see below two ways:

(1) All column names and datatypes comma separated:

SELECT     
table_name, 
string_agg(column_name,',') as "Columns", 
string_agg(data_type ,',') as "DataTypes"
 FROM 
 information_schema.columns
 where "table_schema"='public'
  group by table_name

(2) All column names separately with datatypes

SELECT 
table_name, 
column_name as "Columns", 
data_type as "DataTypes"
 FROM 
 information_schema.columns
 where "table_schema"='public'
  order by table_name,column_name