Description: Is there an ODBC query to retrieve Tables, Columns, Column Datatype, Domains and Domain Datatypes in erwin Data Modeler?
Yes, you can us the following to get the above mentioned Objects and their properties as follows:
TRAN(PEn.Physical_Name) 'Table Name',
TRAN(PAt.Physical_Name) 'Column Name',
PAt.Physical_Data_type 'Column Datatype',
TRAN(PDo.Physical_Name) 'Domain Name',
TRAN(PDo.Physical_Data_Type) 'Domain Datatype'
INNER JOIN EM0.MV_Physical_Entity@ PEn
ON PEn.Id@ = PAt.Owner@
LEFT OUTER JOIN EM0.MV_Physical_Domain@ PDo
ON PDo.Id@ = PAt.Parent_Domain_Ref
ORDER BY 1,2
Additionally, you can also see some more sample queries which are provide with the install of the application. Those queries are in the folder where erwin DM is installed at and are in the folder:
\\ODBC Reports\ODBC Sample Reports
You can use those queries as a starting point for other queries. The metadata for the Tables and Columns is also available in the same folder where erwin DM is installed at and is available in the following folder:
\BackupFiles\Samples - the name of the model is 'erwin Relational Metamodel.erwin'.