-- AccessDB Rescue (accessdbrescue.eu): structure of this PostgreSQL database. Read-only, reads no data. -- Returns one row per table, view, procedure, function, trigger and foreign key, with row counts and code size. -- Row counts are PostgreSQL's estimates (pg_class.reltuples). PostgreSQL 11 or newer. -- Save the result as CSV (pgAdmin: "Save results to file"; psql: \copy (...) TO 'structure.csv' CSV HEADER). SELECT 'database' AS object_type, '' AS schema_name, current_database() AS object_name, NULL::bigint AS row_count, NULL::bigint AS column_count, NULL::int AS has_pk, replace(split_part(version(), ',', 1), ',', ' ') AS ref_name, NULL::int AS code_lines UNION ALL SELECT 'table', n.nspname, c.relname, greatest(c.reltuples, 0)::bigint, (SELECT count(*) FROM pg_attribute a WHERE a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped), CASE WHEN EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p') THEN 1 ELSE 0 END, NULL, NULL FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r', 'p') AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname NOT LIKE 'pg_toast%' UNION ALL SELECT 'view', n.nspname, c.relname, NULL, NULL, NULL, NULL, array_length(string_to_array(pg_get_viewdef(c.oid), E'\n'), 1) FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('v', 'm') AND n.nspname NOT IN ('pg_catalog', 'information_schema') UNION ALL SELECT CASE p.prokind WHEN 'p' THEN 'procedure' ELSE 'function' END, n.nspname, p.proname, NULL, NULL, NULL, NULL, array_length(string_to_array(p.prosrc, E'\n'), 1) FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.objid = p.oid AND d.deptype = 'e') UNION ALL SELECT 'trigger', n.nspname, t.tgname, NULL, NULL, NULL, c.relname, NULL FROM pg_trigger t JOIN pg_class c ON c.oid = t.tgrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE NOT t.tgisinternal AND n.nspname NOT IN ('pg_catalog', 'information_schema') UNION ALL SELECT 'foreign_key', n.nspname, c.relname, NULL, NULL, NULL, r.relname, NULL FROM pg_constraint k JOIN pg_class c ON c.oid = k.conrelid JOIN pg_class r ON r.oid = k.confrelid JOIN pg_namespace n ON n.oid = c.relnamespace WHERE k.contype = 'f' ORDER BY 1, 2, 3;