用SQL语句导出Dynamics 365/CRM的表结构

with attr as
(
select b.Name as EntityName, b.originalLocalizedName as EntityNote
,a.PhysicalName,a.AttributeId,a.IsCustomField,a.Length,a.IsNullable
,a.AttributeTypeId,a.validforcreateapi
from MetadataSchema.Attribute as a
join MetadataSchema.Entity as b ON
a.IsCustomField = 1
and b.IsCustomEntity=1
and a.EntityId=b.entityid
–and b.Name=‘new_EntityType’
)
select a.EntityName as TableName,a.EntityNote as TableDesc,PhysicalName as ColumnName,
label.Label as ColumnDesc,ty.Description as ‘Type’,
(select top 1 name from MetadataSchema.Entity
where EntityId in
( select ship.ReferencedEntityId
from MetadataSchema.Relationship as ship
where ship.ReferencingAttributeId = a.AttributeId)
) as ‘Default’
,a.IsCustomField,a.Length
,NullAble=CASE WHEN a.IsNullable=1 THEN N’√’ELSE N’’ END
from attr as a
inner join MetadataSchema.LocalizedLabel as label
on a.attributeid = label.objectid
inner join MetadataSchema.AttributeTypes as ty
on a.AttributeTypeId = ty.AttributeTypeId
where label.objectcolumnname = ‘DisplayName’ and a.validforcreateapi = 1

ORDER BY entityname,ColumnName