xTuple.com xTupleU Blog & News Customer Support

PostgreSQL code to generator a data dictionary

Technology
#1

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?

#2

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