DvSchemaSync can synchronize most Dataverse tables to Azure SQL Database. This page explains which tables are supported and any limitations.
Table Types
Custom Tables (Entities):
All custom tables created in your Dataverse environment are fully supported. This includes tables with custom prefixes (e.g., cr123_customtable) as well as tables from installed solutions.
Standard Tables:
Common Microsoft Dataverse standard tables are supported, including Account, Contact, Lead, Opportunity, Case, and most other CRM-related tables.
System Tables:
Limited system tables are available for synchronization. These include tables like SystemUser, Team, BusinessUnit, and TransactionCurrency that are often needed for reporting relationships.
Commonly Synchronized Tables
The following standard tables are frequently synchronized for reporting purposes:
| Table Name | Logical Name | Notes |
|---|---|---|
| Account | account | Organizations, companies |
| Contact | contact | Individual people |
| Lead | lead | Sales leads |
| Opportunity | opportunity | Sales opportunities |
| Case | incident | Service cases/tickets |
| Activity | activitypointer | Emails, calls, tasks, etc. |
| System User | systemuser | Users for ownership lookups |
| Team | team | Team assignments |
| Business Unit | businessunit | Organizational hierarchy |
| Currency | transactioncurrency | Currency references |
Limitations
Tables that cannot be synchronized:
• Virtual tables — Tables backed by external data sources cannot be synced
• Elastic tables — Azure Cosmos DB-backed tables have different query patterns
• Image/File columns — Binary data columns are skipped during synchronization
💡 Tip: If a table doesn't appear in the selection list, it may be a virtual table or may not have read permissions granted to your application user.
Tables Created by DvSchemaSync
In addition to synchronizing Dataverse tables, DvSchemaSync creates several system tables in your SQL database to support option set lookups, audit history, and richer reporting.
Option Set Tables
optionset (Local Option Sets)
A consolidated table containing all local option set (picklist) definitions from all synchronized entities. Created when you select "OptionSet (sys)" in the System Tables panel.
| Column | Type | Description |
|---|---|---|
| id | INT IDENTITY | Auto-generated primary key |
| entity_logical_name | NVARCHAR(100) | The table containing this option set (e.g., account) |
| attribute_logical_name | NVARCHAR(100) | The column name (e.g., industrycode) |
| option_value | INT | The numeric value of the option |
| option_label | NVARCHAR(400) | The display label for the option |
| option_lcid | INT | Language code (default 1033 = English) |
| option_color | NVARCHAR(50) | Color code if defined in Dataverse |
| state_code | INT | Associated state (for status reason options) |
status_optionset (Status & Status Reason)
A consolidated table specifically for status-related option sets (statecode, statuscode, and picklists). Created when you select "Status", "StatusReason", or "Picklists" in the System Tables panel.
Schema is identical to the optionset table, allowing you to query status values separately from general picklists.
Audit Tables
audit (Audit Logs)
Contains Dataverse audit log records showing who changed what and when. Created when you select "audit" in the System Tables panel. Requires auditing to be enabled in your Dataverse environment.
| Column | Type | Description |
|---|---|---|
| auditid | UNIQUEIDENTIFIER | Primary key - unique audit record ID |
| action | INT | Action code (1=Create, 2=Update, 3=Delete, etc.) |
| action_name | NVARCHAR(100) | Human-readable action name |
| objectid | UNIQUEIDENTIFIER | ID of the record that was changed |
| objecttypecode | INT | Entity type code of the changed record |
| entity_name | NVARCHAR(128) | Logical name of the entity (e.g., contact) |
| userid | UNIQUEIDENTIFIER | ID of the user who made the change |
| createdon | DATETIME2 | When the change occurred |
| changedata | NVARCHAR(MAX) | JSON containing detailed attribute changes |
| synced_on | DATETIME2 | When this record was synced to SQL |
auditdetail (Audit Attribute Changes)
Contains detailed attribute-level changes extracted from the audit table's changedata JSON. Each row represents one field that changed, with old and new values. Automatically created when syncing audit logs.
| Column | Type | Description |
|---|---|---|
| auditdetailid | UNIQUEIDENTIFIER | Primary key - unique detail record ID |
| auditid | UNIQUEIDENTIFIER | Foreign key to the audit table |
| attributename | NVARCHAR(100) | Logical name of the changed field |
| oldvalue | NVARCHAR(MAX) | Previous value (raw) |
| newvalue | NVARCHAR(MAX) | New value (raw) |
| oldvalue_formatted | NVARCHAR(MAX) | Previous value (display label) |
| newvalue_formatted | NVARCHAR(MAX) | New value (display label) |
| changedate | DATETIME2 | When the change occurred |
Example: Query Audit History with Details
SELECT a.entity_name, a.action_name, a.createdon, d.attributename, d.oldvalue_formatted, d.newvalue_formatted FROM audit a JOIN auditdetail d ON d.auditid = a.auditid WHERE a.entity_name = 'contact' ORDER BY a.createdon DESC
✓ Benefits: These system tables enable you to build audit trail reports showing who changed what and when, create compliance dashboards, and resolve option set values to labels without manual lookups.