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

Use GetSchema() : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=netframework-4.8 — jdweng, Apr 27 '19 at 08:30
@DanGuzman - I can understand your eagerness to mock on the question rather than providing insightful information (which will actually require some efforts). I explicitly mentioned that it is a hypothetical query to show that there maybe multiple types of objects that i may need to extract and identify from the query. While writing this scenario i thought i may need to spoon feed each and every word but did not expect do it for you (MS MVP). So the secret is “HYPOTHETICAL SCENARIO”. If you really want to help then i would say use your experience and create something that can help community. — CuriousKid, Apr 27 '19 at 21:43
Could you maybe do a pattern lookup for ```'{from or join} [A-Za-z_]'```, and then capture everything to the right of the space? This would at least get you the object names, I think? — Cowthulhu, Apr 29 '19 at 17:07
@Cowthulhu , That is one option but not a practical one with accuracy for huge number of queries with different styles of writing. I am hoping to check jdweng's comment link if i can utilize the sqlconnection.getSchema method to get the dependencies. Since it is .net and I am not much familiar with it that is why it is taking time for me to explore that part. — CuriousKid, Apr 29 '19 at 23:55
what about creating view/function and wrap the query into that function? Then you'll have physical object and you'll be able to get all dependencies as for usual view or function — Dmitrij Kultasev, Jul 23 '19 at 09:22
You could try to get the execution plan and parse that. It has all the objects in there. — mxix, Jul 23 '19 at 15:33
Could you save the query as a temporary stored procedure: Create procedure #usptemp as ... your query here. Then use sp_depends to find the dependencies — Steve Ford, Jul 24 '19 at 14:19

Hadi · Answer 1 · 2019-07-22T22:50:08.370

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:

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.

Using a SQL parser, as example:
- SQL Parser
- SQL Parser - Code Project

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

score 1 · Answer 2 · answered Jul 24 '19 at 08:38

1

If your reports are connecting to SQLServer and you have access you could try to get the execution plan with SET SHOWPLAN_XML ON and parse it.

Relevant thread for the parsing:extracting-data-from-sql-servers-xml-execution-plan

answered Jul 24 '19 at 08:38

mxix

3,539
1
16
23

Do you know if i am running SET SHOWPLAN_XML ON and getting the xml in my resultset then how can insert it in a Table? I am struggling with inserting the xml into a column in a table. – CuriousKid Jul 25 '19 at 23:22
I am using dynamic sql to run through couple hundred queries and want to store Execution Plan XML of all the queries into a table. – CuriousKid Jul 25 '19 at 23:27

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

2 Answers2