Glen Mazza's Weblog

The following SOAP request to the Salesforce Marketing Cloud API, run within SFMC's Postman Collection, will retrieve, in a single SOAP response, all SQL Query Activities in your business unit, including the query text and the data extensions they load:

<?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>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</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>QueryDefinition</ObjectType>
                <Properties>Name</Properties>
                <Properties>TargetUpdateType</Properties>
                <Properties>Description</Properties>
                <Properties>CustomerKey</Properties>
                <Properties>QueryText</Properties>
                <Properties>Status</Properties>
                <Properties>DataExtensionTarget.Name</Properties>
                <Properties>DataExtensionTarget.CustomerKey</Properties>
                <Filter xsi:type="SimpleFilterPart">
                    <Property>Status</Property>
                    <SimpleOperator>equals</SimpleOperator>
                    <!--SimpleOperator>notEquals</SimpleOperator-->
                    <Value>Active</Value>
                </Filter>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>

By filtering on active/not active, you can also retrieve deleted SQL queries. SFMC apparently does a "soft delete" for at least some period of time after a SQL activity is removed, helpful if you need to retrieve something accidentally discarded.

With all the data in a single text-searchable SOAP response, it becomes much easier to efficiently match SQL queries to the data extensions they read from or write to. It can help identify activities missing descriptions or readable customer keys. Using SOAP also allows you to avoid the one-by-one inspecting in the Automation Studio UI with all its annoying confirmation and warning boxes.

The query below retrieves all data extensions in your business unit. Crossing-out those referenced by the SQL queries above can help identify data extensions perhaps no longer needed. Of course, many data extensions are read from and/or written to outside of SQL queries (such as API calls, internal SFMC processing, and SFMC add-ons), so further investigation beyond non-usage in a query activity would be needed before deleting a data extension.

<?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>
        <a:Action s:mustUnderstand="1">Retrieve</a:Action>
        <a:To s:mustUnderstand="1">https://{{et_subdomain}}.soap.marketingcloudapis.com/Service.asmx</a:To>
        <fueloauth xmlns="http://exacttarget.com">{{dne_etAccessToken}}</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>DataExtension</ObjectType>
                <Properties>ObjectID</Properties>
                <Properties>CustomerKey</Properties>
                <Properties>Description</Properties>
                <Properties>Name</Properties>
                <Properties>IsSendable</Properties>
                <Properties>SendableSubscriberField.Name</Properties>
            </RetrieveRequest>
        </RetrieveRequestMsg>
    </s:Body>
</s:Envelope>