1

I am aware by using SOAP api as we can find the Query Activies, which used specific DE as Target Data Extension

How to find query that populated a marketing cloud data extention?

But my requirement is exactly the opposite.

I want to know the list of SQL query Activities which uses my data extension in Select statements

Any ideas would help, any method APIs, WSproxy!

4 Answers 4

1

This is a solution for those who do not know anything about Cloud Pages or how to send a SOAP message to the MC API.

Combining the other answers, as well as a script by Zuzanna Jarczynska that I also found, I came to this script:

<script runat="server">
Platform.Load("Core", "1");
try {
    var prox = new Script.Util.WSProxy();
    var targetDE = 'AAAAAAAA-BBBB-CCCC-DDDD-EEEEEEEEEEEE'; // External key of DE where search results are stored
    var logDE = DataExtension.Init(targetDE);

    function getQueries(objectType, cols) {
        var objectType = "QueryDefinition",
            cols = ["Name", "Description", "QueryText", "ObjectID", "DataExtensionTarget.Name", "DataExtensionTarget.CustomerKey"],
            filter = {
                 Property: "QueryText",
                 SimpleOperator: "like",
                 Value: "whatever you are looking for" // The text that you are looking for in all of your SQL queries
            }
            moreData = true,
            reqID = null,
            numItems = 0;
        while (moreData) {
            moreData = false;
            var data = reqID == null ? prox.retrieve(objectType, cols, filter) : prox.getNextBatch(objectType, reqID);
            if (data != null) {
                moreData = data.HasMoreRows;
                reqID = data.RequestID;
                if (data && data.Results) {
                    for (var i = 0; i < data.Results.length; i++) {
                        logDE.Rows.Add({
                            Name: data.Results[i].DataExtensionTarget.Name,
                            DEKey: data.Results[i].DataExtensionTarget.CustomerKey,
                            ActivityName: data.Results[i].Name,
                            ActivityType: "Query",
                            ActivityDescription: data.Results[i].Description
                        });
                        numItems++;
                    }
                }
            }
        }
        Write("<br />" + numItems + " total " + objectType + "<br>");
    }
    getQueries(objectType, cols)
} catch (e) {
    Write(Stringify(e));
}
</script>

This must be stored as a script in Automation Studio Activities. And then run, of course (via Run once).

But! It requires a Data Extension in which the results are stored. You have to create this first. Columns are: Name, DEKey, ActivityName, ActivityType, ActivityDescription. These are all text fields, size 250.

As SQL queries can easily be larger than 250 characters, this script does not store the queries itself. But the name of the query (in Name) points you in the right direction.

5

As an alternative to the solution posted above, here's a WSProxy solution that uses a filter to search all queries and target DE's that reference a particular data extension:

    <script runat="server">
      Platform.Load("core","1.1.5");
      var prox = new Script.Util.WSProxy();
    
      // REPLACE VALUE WITH THE DE NAME YOU WANT TO SEARCH FOR
      var sourceDE = "Some Data Extension";
      
      var cols = ["Name","CategoryID","DataExtensionTarget.Name","QueryText","CreatedDate","ModifiedDate","CategoryID","TargetUpdateType","Status"];
        var filter = {
             LeftOperand: {
                 Property: "QueryText",
                 SimpleOperator: "like",
                 Value: sourceDE
             },
             LogicalOperator: "OR",
             RightOperand: {
                 Property: "DataExtensionTarget.Name",
                 SimpleOperator: "equals",
                 Value: sourceDE
             }
         };
        var data = prox.retrieve("QueryDefinition", cols, filter)
        var json = data.Results;
    </script>
2
  • How and where can I run this script? Sep 29, 2022 at 10:46
  • Like any other SSJS - you can run it in either a Cloud Page or Script Activity in Automation Studio Sep 29, 2022 at 11:14
1

You can retrieve all QueryDefinitions from a BU, the full query (the select statement) is in the property "QueryText".

If you are looking for a specific data extension, then you will have to loop through all responses, for each result isolate what is enclosed in <QueryText>SELECT...</QueryText> and basically do a text search for your DE name. (e.g. using indexOf in scripts, or PATINDEX in T-SQL)

For this, you could just add the result of your API call to a DE field; Create that DE via API as you do not want a maxLength on said field;

Just be aware that if the data extension name you are looking for is also in a /* comment */ that will be a "false positive". Depending on how much effort you want to invest in your search parser, There might be a way around this with regex, if it really an issue.

here's the retrieve via API:

<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <SOAP-ENV:Header>
       <fueloauth>{{accessToken}}</fueloauth>
    </SOAP-ENV:Header>
    <SOAP-ENV:Body>
      <RetrieveRequestMsg xmlns="http://exacttarget.com/wsdl/partnerAPI">;
        <RetrieveRequest>
            <ObjectType>QueryDefinition</ObjectType>
                <Properties>CustomerKey</Properties>
                <Properties>Name</Properties>
                <Properties>Client.ID</Properties>
                <Properties>ObjectID</Properties>
                <Properties>QueryText</Properties>
         </RetrieveRequest>
      </RetrieveRequestMsg>
    </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
4
  • Thank you so much @ Jonas.... your answer gave me idea! ... though of using filter as below, where in I can search DE name within query ... seems like this works... any thoughts on this.... <Filter xsi:type="SimpleFilterPart"><Property>QueryText</Property><SimpleOperator>like</SimpleOperator><Value>MyDEname</Value></Filter> Aug 16, 2022 at 14:11
  • 1
    true that, of course you can directly filter using Like - same caveat, be careful with comments. Aug 16, 2022 at 15:13
  • To which URL should this message be sent and how do I get an access token? Sep 29, 2022 at 10:47
  • 1
    @SanderdeJong When connecting to the SOAP API, use the WSDL file and service endpoint that are shown in Account Settings. And your token can be retrieved through the AUTH REST call, using client ID and client secret. Sep 29, 2022 at 11:13
0

Use the below script in the cloud page to get the list of SQL query Activities that uses specific data extension in Select statements:

<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>SQL Query Activity Details</title>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</head>
<body>
    <h1 id="pageTitle">SQL Query Activity Details</h1>
    <form id="searchForm">
        <input type="text" id="searchInput" placeholder="Search...">
        <input type="submit" value="Search">
    </form>
    <div id="resultContainer"></div>

    <script>
        $(document).ready(function() {
            $('#searchForm').submit(function(event) {
                event.preventDefault();
                var filterValue = $('#searchInput').val();

                $.ajax({
                    type: "POST",
                    url: "%%=RequestParameter('PAGEURL')=%%",
                    data: {
                        searchInput: filterValue
                    },
                    success: function(response) {
                        $('#resultContainer').html(response);
                        $('#pageTitle').hide(); 
                        $('#searchForm').hide(); 
                    }
                });
            });
        });
    </script>

    <script runat="server">
        Platform.Load("Core", "1.1.1");

        try {
            var filterValue = Request.GetFormField("searchInput");

            if (filterValue) {
                var rr = Platform.Function.CreateObject("RetrieveRequest");
                Platform.Function.SetObjectProperty(rr, "ObjectType", "QueryDefinition");
                Platform.Function.AddObjectArrayItem(rr, "Properties", "Name");
                Platform.Function.AddObjectArrayItem(rr, "Properties", "QueryText");
                Platform.Function.AddObjectArrayItem(rr, "Properties", "CreatedDate");
                Platform.Function.AddObjectArrayItem(rr, "Properties", "DataExtensionTarget"); 
                var filter = Platform.Function.CreateObject("SimpleFilterPart");
                Platform.Function.SetObjectProperty(filter, "Property", "QueryText");
                Platform.Function.SetObjectProperty(filter, "SimpleOperator", "like");
                Platform.Function.AddObjectArrayItem(filter, "Value", "%" + filterValue + "%");
                Platform.Function.SetObjectProperty(rr, "Filter", filter);

                var retrieveStatus = [0, 0, 0];
                var queryDefResultSet = Platform.Function.InvokeRetrieve(rr, retrieveStatus);

                if (queryDefResultSet && queryDefResultSet.length > 0) {
                    var output = "<table>";
                    output += "<tr><th>Query Name</th><th>Query Text</th><th>Created Date</th><th>Data Extension Target</th></tr>";

                    for (var i = 0; i < queryDefResultSet.length; i++) {
                        var queryDefRow = queryDefResultSet[i];
                        var queryName = queryDefRow["Name"];
                        var queryText = queryDefRow["QueryText"];
                        var createdDate = queryDefRow["CreatedDate"];
                        var dataExtensionTarget = queryDefRow["DataExtensionTarget"]; 

                        output += "<tr>";
                        output += "<td>" + queryName + "</td>";
                        output += "<td>" + queryText + "</td>";
                        output += "<td>" + createdDate + "</td>";
                        output += "<td>" + dataExtensionTarget + "</td>"; 
                        output += "</tr>";
                    }

                    output += "</table>";
                    Write(output);
                } else {
                    Write("<p>No SQL Query activities found.</p>");
                }
            }
        } catch (error) {
            Write("<p>An error occurred while retrieving SQL Query activities: " + error + "</p>");
        }
    </script>
</body>
</html>

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .