dump_tables.sql

gistfile1.txt

SELECT json_agg(
	json_build_object(
		'table', table_name,
		'comment', table_comment,
		'columns', columns
	) ORDER BY table_name
)
FROM (
	SELECT
		c.relname as table_name,
		d_table.description as table_comment,
		(
			SELECT json_agg(
				json_build_object(
					'name', a.attname,
					'type', pg_catalog.format_type(a.atttypid, a.atttypmod),
					'comment', d.description
				) ORDER BY a.attnum
			)
			FROM pg_attribute a
			LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.objsubid = a.attnum
			WHERE a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
		) as columns
	FROM pg_class c
	JOIN pg_namespace n ON n.oid = c.relnamespace
	LEFT JOIN pg_description d_table ON d_table.objoid = c.oid AND d_table.objsubid = 0
	WHERE n.nspname = 'contentdb' AND c.relkind = 'r' AND c.relname != 'flyway_schema_history'
) tables