How to fetch Marketing cloud DataExtention Metadata using API?

Question

How can we fetch the properties of a Marketing cloud Data Extention using it's API. For Example :- there is a Data extention named "ABC" and having feilds(properties) like name(String), age(integer), etc.

I want to fetch the properties of Data Extention with it's data type via API so that I have the information that the DE "ABC" has properties name,age,etc with data type String,integer,etc respectively.

Answer 1

You can do a SOAP Post call leveraging DataExtensionField object.

The SOAPAction should be "Retrieve"

The payload should be something similar to:

<?xml version="1.0" encoding="UTF-8"?>
<s:Envelope xmlns:s="http://www.w3.org/2003/05/soap-envelope" xmlns:a="http://schemas.xmlsoap.org/ws/2004/08/addressing" xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
   <s:Header>
      <fueloauth xmlns="http://exacttarget.com">{{access_token}}</fueloauth>
   </s:Header>
   <s:Body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
     <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">
                     <RetrieveRequest>
                        <ObjectType>DataExtensionField</ObjectType>
                        <Properties>FieldType</Properties>
                        <Properties>Name</Properties>
                        <Properties>IsRequired</Properties>
                        <Properties>Scale</Properties>  
                        <Filter xsi:type="SimpleFilterPart">
                           <Property>DataExtension.CustomerKey</Property>
                           <SimpleOperator>equals</SimpleOperator>
                           <Value>DE_CUSTOMER_KEY</Value>
                        </Filter>
                     </RetrieveRequest>
                  </RetrieveRequestMsg>  
   </s:Body>
</s:Envelope>

You can get use more properties on your payload to get more information about your fields, like:

<Properties>DefaultValue</Properties>
<Properties>MaxLength</Properties>
<Properties>CreatedDate</Properties>

More properties to be found in the link to the DataExtensionField documentation provided above.

PS: Don't forget to add data_extensions_read scope to your authentication payload, and to your package on Marketing Cloud as well.

For a step by step guide, checkout Gregory's latest article: Introduction to making API calls to SFMC from POSTman