What is the best way to find a database schema that lays out every table and field in Epicor?
If you’re looking for Epicor-provided system information, I would take a look at the data dictionary form which should be within the system maintenance menu.
1 Like
Is there a table to do a sql query lookup of all the tables and fields?
It sounds like you want to execute this general SQL after selecting your Epicor database:
SELECT o.name AS tbl, c.name AS col, t.name AS type, t.max_length AS size
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.system_type_id = t.system_type_id
WHERE o.type_desc = 'USER_TABLE'
The query should return the table, column, type, and size for the tables in the database.
1 Like
Also, this is a good article
Did you take a look at this?
Is there a field description column that can be included in the sql query?
zdatafield.dbtablename, zdatafield.dbfieldname,zdatafield.description contains most of what you are looking for.
3 Likes
I was waiting until I got home to post what @aidacra just posted. This is the Epicor listing of the tables and fields as organized by the application.
I stopped using the Data Dictionary for quite a while, it is just inconvenient
There’s probably one from old yahoo group, generated by HtmlDict, like this but at E905
http://epidd.wisconsinconverting.com/
It doesn’t have graphs or ER diagram, but at least you can see descriptions, and a list of tables having the same fieldname.
I actually created a dashboard from a query that utilizes the “zDataTable” and “zDataField” tables along with a tracker Matches(*) criteria fields on the table/field name/description. These tables list out all of the tables and field located in the database. These are the fields that I utilize in the tables you’re probably looking for:
zDataTable.DBTableName, zDataTable.Description
zDataField.FieldName, zDataField.Description, zDataField.DataType