Find all SQL Query Activities which contain specific DataExtension in Query

Question

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!

Answer 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.

Answer 2

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>

Answer 3

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>

Answer 4

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>