If you are like me sometimes it can be hard to translate from technical name to the actual data entity name. As a Microsoft Dynamics 365 for Finance and Operations developer it is sometimes useful to use the cross reference to identify the proper entity to use, however some tables are used so often it can be a task to scroll through to find the right reference.  An example is EcoResCategory table.

Each data entity is actually a SQL view as well.  So we can back into a list of tables used on each data entity using a SQL query!

Let’s find out which entities use the EcoResCategory table.

Surprise!  There are 70 data entities using that table (I chose this on purpose).  This is because it is much easier to give the category name instead of a RefRecId.  So there is still some brainpower needed here, but it normally is a very short list to review.  I suggest that you export this list once, and refer to it whenever required.  Here is the list for 10.0.0.

Here is the SQL Statement to run which will list the tables referenced in all queries used by data entities.

SELECT view_name, Table_Name
FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
WHERE view_name like ‘%entity’
ORDER BY view_name, table_name

I’ve also used this approach to identify the list of custom tables which do not have a data entity (I had to use VLOOKUP in Excel though).  This was important to me because I want to be able to use templates and configuration data packages to more easily migrate data between environments.