Dataverse - How to retrieve FetchXML or SQL for Dataverse views

With, Dataverse It's possible to define views. Dataverse stores view definitions in a system table called 'Views'. We can interrogate this table to retrieve the SQL or the FetchXML for a table and this post examines this setup in more detail.

With Dataverse, we can define views which enable us to carry out basic joins, and to filter and to sort data server-side.

As an example, here's the definition of a view that's based on a custom table called 'Property'. This view retrieves the address, city, and property type conlumns from this table, and inlcudes a filter to return only records where the property type matches 'house'. The name of this view is 'houses'.

Dataverse stores the definition of this view in a standard table called Views. If we open this table in the Dataverse designer, we notice two interesting columns - FetchXML and 'Offine SQL Query'.

By default, it isn't possible to view the contents of this table in the designer - the 'Data' tab is missing from the designer

However, we can access this table through other means, such as through the TDS endpoint, or by building an app that's based on this table.

The screenshot beneath illustrates an auto-generated app that's based on the 'Views' tables, and highlights how we can display the FetchXML and SQL definition of the 'houses' view.


Dataverse stores view definitions in a table called Views. We can access the contents of this table and use it to discover the FetchXML and SQL definitions of the Dataverse views.