Skip to main content

Notifications

Customer experience | Sales, Customer Insights,...
Suggested answer

SQL Query Tables Size from Dataverse / Dynamics 365

(0) ShareShare
ReportReport
Posted on by 5

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?

  • Suggested answer
    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 Profile Picture
    PerezAguiar on at
    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.

  • 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 Profile Picture
    Bipin D365 28,964 Super User 2024 Season 1 on at
    RE: SQL Query Tables Size from Dataverse / Dynamics 365

    Hi,

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

    5078.CR58.PNG

    Please mark my answer verified if this is helpful!

    Regards,

    Bipin Kumar

    Follow my Blog: xrmdynamicscrm.wordpress.com/

  • PerezAguiar Profile Picture
    PerezAguiar on at
    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans