Retrieving Field Descriptions

Example
Results

Retrieving Field Descriptions

This page shows how to retrieve field description metadata from Salesforce by using the SF_Metadata stored procedure, which retrieves metadata through the Salesforce Metadata API.

Example

The following example creates an Input table, retrieves metadata for all custom objects, filters out unsupported entries, and then parses the returned XML to extract field names and their descriptions:

DROP TABLE MD_FieldDesc
GO

CREATE TABLE MD_FieldDesc (
[Name] nvarchar(255) NULL,
[Member] nvarchar(255) NULL,
[MetadataXML] xml NULL,
[CreatedByWildcard] bit NULL,
[CreatedByList] bit NULL,
[Error] nvarchar(255) NULL,
[ID] nchar(18) NULL
)

INSERT INTO MD_FieldDesc (Name, Member) VALUES('CustomObject', '*')

-- Get a list of objects with customer fields
EXEC SF_Metadata 'List', 'Salesforce', 'MD_FieldDesc'

-- Cleanup wildcard and objects that will error
DELETE MD_FieldDesc WHERE Member = '*'
DELETE MD_FieldDesc WHERE Member = 'SiteChangeList'

-- Retrieve the field metadata
EXEC SF_Metadata 'Retrieve', 'Salesforce', 'MD_FieldDesc'

-- Query to select descriptions
;WITH XMLNAMESPACES(DEFAULT 'http://soap.sforce.com/2006/04/metadata') SELECT Member
,fn.c.value('(fullName)[1]','nvarchar(50)') as FieldName
,fn.c.value('(description)[1]','nvarchar(50)') as Description
,fn.c.value('(../fullName)[1]','nvarchar(50)') as PicklistValue
,fn.c.value('(.)[1]','nvarchar(50)') as ControllingPicklistValue FROM MD_FieldDesc
cross apply metadataxml.nodes ('/CustomObject/fields') as fn(c)

Results

The code example above produces a table similar to the one shown below:

Member                         FieldName                                           Description  PicklistValue   ControllingPicklistValue
-----------------------------  --------------------------------------------------  -----------  --------------  ----------------------------------------
Wide_Object__c                 asdasldkjasldkjalskdjalskdjalskdjalksdja__c         NULL         NULL            asdasldkjasldkjalskdjalskdjalskdjalksdja__cfalseas
Wide_Object__c                 asdkljasdlkjasl_kdj_eohgaw_eugh_gha_dgha__c         NULL         NULL            asdkljasdlkjasl_kdj_eohgaw_eugh_gha_dgha__cfalseas
Wide_Object__c                 awdawdawdawdawdawdawdawdawdawdawdawdawda__c         NULL         NULL            awdawdawdawdawdawdawdawdawdawdawdawdawda__cfalseaw
Wide_Object__c                 qweqweqweqweqeqweqweqweqweqweqweqweqweqw__c         NULL         NULL            qweqweqweqweqeqweqweqweqweqweqweqweqweqw__cfalseqw
Wide_Object__c                 zxczxczxczxczxczxczxczxczxczxczxczxczxcz__c         NULL         NULL            zxczxczxczxczxczxczxczxczxczxczxczxczxcz__cfalsezx

PromotionSegmentSalesStore     PromotionSegmentId                                  NULL         NULL            PromotionSegmentIdfalseMasterDetail
PromotionSegmentSalesStore     SalesStoreId                                        NULL         NULL            SalesStoreIdfalseLookup

ContactPointTypeConsent        BusinessBrandId                                     NULL         NULL            BusinessBrandIdfalseLookup
ContactPointTypeConsent        CaptureContactPointType                             NULL         NULL            CaptureContactPointTypefalse
ContactPointTypeConsent        CaptureDate                                         NULL         NULL            CaptureDatefalse
ContactPointTypeConsent        CaptureSource                                       NULL         NULL            CaptureSourcefalse
ContactPointTypeConsent        ContactPointType                                    NULL         NULL            ContactPointTypefalse
ContactPointTypeConsent        DataUsePurposeId                                    NULL         NULL            DataUsePurposeIdfalseLookup
ContactPointTypeConsent        DoubleConsentCaptureDate                            NULL         NULL            DoubleConsentCaptureDatefalse
ContactPointTypeConsent        EffectiveFrom                                       NULL         NULL            EffectiveFromfalse
ContactPointTypeConsent        EffectiveTo                                         NULL         NULL            EffectiveTofalse      
...More output lines...