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

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#

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