We are trying to obtain a data dictionary of the tables of Xtuple data. I was told that there is a PostgreSQL script that can run and generate this. Anyone know where I can get this script?
There isn't a script necessarily, PostgreSQL exposes a lot of that information via a special hidden schema in the database called information_schema, so you could use a query like:
SELECT *
FROM information_schema.tables;
To get a list of ALL tables. There is a lot of information available there, so you could use a query like below to get the tables AND columns:
SELECT *
FROM information_schema.tables t
JOIN information_schema.columns c ON (t.table_name = c.table_name)
ORDER BY t.table_schema, t.table_name, c.column_name;
Additionally you can filter this list down, if you only care about a specific schema or table name:
SELECT *
FROM information_schema.tables t
JOIN information_schema.columns c ON (t.table_name = c.table_name)
WHERE t.table_schema = 'public'
--AND table_name = 'your_table' -- uncomment to look at specific table
ORDER BY t.table_schema, t.table_name, c.column_name;
The other part of what you could do is use a tool like SchemaSpy to connect to the database and generate fancy HTML pages with the database schemas all mapped out.
David