D365 - Commonly Used SQL

Table of contents

Querying all views under a specific entity

-- Example: Query all views and their Ids under the Account entity
SELECT 
    v.SavedQueryId AS 'View Id', 
    v.Name AS 'View name',
    v.returnedtypecode AS 'Owning entity'
FROM 
    SavedQuery v
WHERE 
    returnedtypecode = 'account'

Querying reports/report Ids

SELECT reportid,
       name,
       filename,
       description,
       owneridname,
       componentstatename,
       createdon,
       createdbyname
FROM   report;

Querying Annotation file size

-- Warning: May freeze if data is large
-- 1 MB = 1024 * 1024 Bytes = 1,048,576 Bytes
SELECT SUM(filesize) / 1048576 AS TotalFileSizeMB
FROM   annotation;

Finding users assigned to a specific security role

SELECT 
    systemuser.fullname AS UserFullName,
    systemuser.domainname AS DomainName,
    systemuser.systemuserid AS UserId,
    role.name AS RoleName
FROM 
    systemuserroles
INNER JOIN 
    systemuser ON systemuserroles.systemuserid = systemuser.systemuserid
INNER JOIN 
    role ON systemuserroles.roleid = role.roleid
WHERE 
    role.name = 'Pre-sales' -- Security role name

Query result:

Finding security roles for users

SELECT 
    su.fullname AS UserFullName,
    su.domainname AS DomainName,
    su.systemuserid AS UserId,
    STRING_AGG(r.name, ', ') AS RoleNames
FROM 
    systemuserroles sur
INNER JOIN 
    systemuser su ON sur.systemuserid = su.systemuserid
INNER JOIN 
    role r ON sur.roleid = r.roleid
GROUP BY 
    su.fullname,
    su.domainname,
    su.systemuserid
ORDER BY 
    su.fullname

Query result: