Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Suggested answer

SQL Query Tables Size from Dataverse / Dynamics 365

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,962 Moderator 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

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,552 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,552 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans