Extract db objects dependencies from SSRS dataset with inline query (Query Type : Text)

Question

I am dealing with a specific problem of identifying the dependent db objects for any SSRS RDL.

I have a good understanding of if any dataset have stored procedure as the query in a RDL then I can reference the associated stored procedure and get all the dependent objects (details can be found here: Different Ways to Find SQL Server Object Dependencies)

But I am looking specifically for the datasets with text query or inline query for any rdl. I am able to extract the CommandText from the XML of the rdl but I am not sure how to extract db objects like sp, table, views columns form a command text which is inline query in the rdl.

For example if I extract below query from XML commandText (this is a hypothetical query, names are not standardized in the database like vw_ for views , udf_ for functions):

    -----This query serves Report ABC
    SELECT DATE
        ,[amount]
        ,teamID = (SELECT TeamID FROM Sales.[getSalesPerson](r.date) as s WHERE R.[SalesPersonName] = S.[SalesPersonName])
        ,[channel]
        ,[product]
        ,[Item]
        ,r.[M_ID]
        ,Amount
        ,M.[Type]
    FROM dbo.FactTable AS R
    LEFT JOIN sp_Channel C ON R.[Channel_ID] = C.[Channel_ID]
    LEFT JOIN Marketing.vw_M M ON R.[M_ID] = M.[M_ID]

Is there a way to identify that this query have dependent object as below:

ObjectName                ObjectType
------------------------------------------
dbo.FactTable             Table 
sp_Channel                Stored Procedure
Marketing.vw_M            View
Sales.[getSalesPerson]    Function

Answer 1

It is not easy to extract object names from an SQL command since they may be written in different ways (with/without schemas, databases name included ...)

But there are many option to extract objects from an SQL query that you can try:

1. Using Regular expressions, As example: You have to search for the words located after the following keywords:

   • TRUNCATE TABLE
   • FROM
   • UPDATE
   • JOIN

The following code is a C# example:

Regex regex = new Regex(@"\bJOIN\s+(?<Retrieve>[a-zA-Z\._\d\[\]]+)\b|\bFROM\s+(?<Retrieve>[a-zA-Z\._\d\[\]]+)\b|\bUPDATE\s+(?<Update>[a-zA-Z\._\d]+)\b|\bINSERT\s+(?:\bINTO\b)?\s+(?<Insert>[a-zA-Z\._\d]+)\b|\bTRUNCATE\s+TABLE\s+(?<Delete>[a-zA-Z\._\d]+)\b|\bDELETE\s+(?:\bFROM\b)?\s+(?<Delete>[a-zA-Z\._\d]+)\b");

var obj = regex.Matches(sql);

foreach(Match m in obj)
{

    Console.WriteLine(m.ToString().Substring(m.ToString().IndexOf(" ")).Trim());

}

Output

Then you have to clean and join the result with the sys.objects tables from the SQL Server database.

2. Using a SQL parser, as example:

   • SQL Parser
   • SQL Parser - Code Project

---

You can refer to the following very helpful links for additional information:

• Regular expression to find all table names in a query
• Parsing SQL code in C#