Welcome to the DataverseSQL repository! This collection showcases useful SQL queries for working with Microsoft Dataverse environments via the TDS (Tabular Data Stream) endpoint. Whether you're troubleshooting, analyzing metadata, or extracting complex relationships, this repository has you covered.
Dataverse supports direct querying using SQL via the TDS endpoint. This repository provides tested and reusable SQL scripts to interact with Dataverse metadata, retrieve business data, and analyze your environment. These queries:
- Enhance productivity by simplifying data analysis.
- Serve as templates for custom scripts tailored to your needs.
- Demonstrate advanced querying techniques for Dataverse environments.
This repository includes scripts for:
- Metadata analysis (tables, columns, relationships, etc.).
- Business entity insights (contacts, accounts, activities, etc.).
- Advanced relationships (hierarchies, lookups, 1:N and N:N relationships).
- Plugin, workflow, and process diagnostics.
Each query is modular and easy to adapt for your specific use cases.
These scripts solve real-world Dataverse challenges, including:
- Identifying unused fields or custom entities.
- Tracking solution dependencies and customizations.
- Monitoring and optimizing system performance.
-
Prerequisites:
-
Access to the Dataverse environment.
-
Enable the TDS endpoint in your environment (admin access required):
https://community.dynamics.com/blogs/post/?postid=dbb726b6-430e-464c-af22-424ed88bcc1a
https://learn.microsoft.com/en-us/power-apps/developer/data-platform/dataverse-sql-query
-
SQL client can connect to the TDS endpoint (e.g., SQL Server Management Studio, Azure Data Studio).
-
-
Connect to the Dataverse TDS Endpoint:
- Use your environment URL as the server name (e.g.,
yourenvironment.crm.dynamics.com). - Authenticate using Azure Active Directory credentials.
- Use your environment URL as the server name (e.g.,
-
Run the Queries:
- Copy the desired SQL query from this repository.
- Paste it into your SQL client.
- Execute the query and review the results.
Contributions are welcome! If you have a useful SQL query for Dataverse, feel free to open a pull request or submit an issue.
Special thanks to the Microsoft Dataverse community for providing tools, guidance, and inspiration for these queries.