Customer experience | Sales, Customer Insights,...

SQL Query Tables Size from Dataverse / Dynamics 365

(0) Share

Report

Posted on by Anderson Oliveira

Good morning everyone!!!
We are looking at the possibility of querying Dataverse/Dynamics 365 data via SQL Server.
I saw that several SYS tables are enabled (eg sys.tables), but some are not (eg sys.partitions).
Well, could someone explain how to query the size of DBO schema tables via SQL Server?

All responses (5)

Answers (0)

Suggested answer

Anderson Oliveira 5 on at

Like (0)

Report

RE: SQL Query Tables Size from Dataverse / Dynamics 365

Thanks again PerezAguiar but my major not completed!!!

I opened this ticket as per your suggestion:

experience.dynamics.com/.../
Suggested answer

PerezAguiar on at

Like (0)

Report

RE: SQL Query Tables Size from Dataverse / Dynamics 365

Hey Anderson.

1) you can perform the query using the regular query structure:

SELECT act.activityid, act.subject, string_agg([to].partyidname, ', ')

FROM activitypointer AS act

LEFT OUTER JOIN activityparty as [to] ON act.activityid = [to].activityid and [to].participationtypemask = 2

GROUP BY act.activityid, act.subject

This is a valid SqlQuery structure and will return values. However, as indicated previously, on docs.microsoft.com/.../dataverse-sql-query there are some limitations:

- 80MB of data of resuls.

- Dates are returned in UTC

- Plugins are not triggered

- They count on the API protection Limits

ON docs.microsoft.com/.../how-dataverse-sql-differs-from-transact-sql you have a list of Supported and NotSupported operations (as Dataverse allows some of SQL commands but not fully). Samples of stuff that won't work:

- Stored procedures

- Security

- Cursors

- Execute

You also have other non suported functions (like Col_Name, Col_length, ColumnProperty, DatabasePropertyEx, DB_ID). Basically: operations with system tables are not allowed.

If you need the storage, you need to go to PowerPlatform admin center. Currently, there's no API to retrieve the storage/Data information. YOu can go to experience.dynamics.com/.../ and check if this feature is already requested or submit your own.
Anderson Oliveira 5 on at

Like (0)

Report

RE: SQL Query Tables Size from Dataverse / Dynamics 365

Thank you for your attention PerezAguiar

But it does not serve the purpose of querying this information via SQL Server...

I believe there is a possibility but we haven't figured it out yet.

I need to do specific queries on the columns

(Eg SELECT colA, colB FROM Table) and the major

I also need to consult the SYS tables to know the size of each table.
Bipin D365 28,964 Super User 2024 Season 1 on at

Like (0)

Report

RE: SQL Query Tables Size from Dataverse / Dynamics 365

Hi,

You can use Power platform admin center to analyze your entity/table size.

Please mark my answer verified if this is helpful!

Regards,

Bipin Kumar

Follow my Blog: xrmdynamicscrm.wordpress.com/
PerezAguiar on at

Like (0)

Report

RE: SQL Query Tables Size from Dataverse / Dynamics 365

Hey Anderson.

Unfortunately that's not an allowed action. You can connect to Dataverse using sql server and perform regular queries to undersand values of records. However most of the functionalities (like storage calculation) are outside of the scope, as well as injecting/updating data using this method.

On this article Tabular Data Stream (TDS) Protocol endpoint for Common Data Service (CDS) | Microsoft Power Apps you have a statement from Microsoft's Development group indicating that only Read actions are allowed. On Use SQL to query data (Microsoft Dataverse) - Power Apps | Microsoft Docs you have the lis of allowed operations

Regards