Dynamics 365项目中常用的SQL语句(持续更新)

归档62

Dynamics 36529 Firefly主题文章示例9 杂物间9 在路上8 Csharp2 MS Office2 Cooking1 Git和Github1 理解计算机1

342 字

2 分钟

Dynamics 365项目中常用的SQL语句(持续更新)

2025-02-17

Dynamics 365

Dynamics 365 Develop

浏览量加载中...

1. 查询指定实体下的所有系统视图#

该查询用于快速检索 Dynamics 365 中特定实体（如客户、联系人）下配置的所有系统视图

1
-- Example: Query all views and their Ids under the Account entity
2
SELECT
3
    v.SavedQueryId AS 'View Id',
4
    v.Name AS 'View name',
5
    v.returnedtypecode AS 'Owning entity'
6
FROM
7
    SavedQuery v
8
WHERE
9
    returnedtypecode = 'account'

2. 检索系统报表清单与元数据#

通过查询报表主表，可以获取环境中所有报表的详细信息，包括创建人、创建时间及文件名称，常用于报表资产盘点或清理闲置报表

1
SELECT reportid,
2
       name,
3
       filename,
4
       description,
5
       owneridname,
6
       componentstatename,
7
       createdon,
8
       createdbyname
9
FROM   report;

3. 查询Annotation容量#

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

4. 查询特定安全角色的所有分配用户#

此脚本用于审计，清晰列出拥有某个特定安全角色（如“售前顾问”）的所有用户

1
SELECT
2
    systemuser.fullname AS UserFullName,
3
    systemuser.domainname AS DomainName,
4
    systemuser.systemuserid AS UserId,
5
    role.name AS RoleName
6
FROM
7
    systemuserroles
8
INNER JOIN
9
    systemuser ON systemuserroles.systemuserid = systemuser.systemuserid
10
INNER JOIN
11
    role ON systemuserroles.roleid = role.roleid
12
WHERE
13
    role.name = 'Pre-sales' -- Security role name

Query result:

UserFullName	DomainName	UserId	RoleName
Zhang San	zhangsan@sample.com	xxx..	Pre-sales
Li Si	lisi@sample.com	xxx..	Pre-sales

5. 查看用户分配了哪些安全角色#

1
SELECT
2
    su.fullname AS UserFullName,
3
    su.domainname AS DomainName,
4
    su.systemuserid AS UserId,
5
    STRING_AGG(r.name, ', ') AS RoleNames
6
FROM
7
    systemuserroles sur
8
INNER JOIN
9
    systemuser su ON sur.systemuserid = su.systemuserid
10
INNER JOIN
11
    role r ON sur.roleid = r.roleid
12
GROUP BY
13
    su.fullname,
14
    su.domainname,
15
    su.systemuserid
16
ORDER BY
17
    su.fullname