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

@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
– CuriousKid, Commented Apr 27, 2019 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
– Cowthulhu, Commented Apr 29, 2019 at 17:07
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
– Dmitrij Kultasev, Commented Jul 23, 2019 at 9:22
You could try to get the execution plan and parse that. It has all the objects in there. — mxix
– mxix, Commented Jul 23, 2019 at 15:33

Hadi · Accepted Answer · 2019-07-22 22:50:08Z

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:

mxix · Accepted Answer · 2019-07-24 08:38:54Z

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, 2019 at 8:38

mxix

3,6591 gold badge18 silver badges25 bronze badges

2 Comments

CuriousKid Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related