PowerShell script to extract T-SQL code from RDL files

One of the things I do in my day job at Microsoft Services is to review database design and T-SQL code. Now, when it comes to code, there is a challenge just to find ALL the possible code which hits our database engine. This is because a non-trivial amount of T-SQL code is not in the form of Stored Procedures, but it is embedded inside other places – such as Reporting Services (SSRS).

Now if you have hundreds of RDL files, extracting T-SQL from those files is a very tedious job. Opening each file in Report Designer, you would have to comb through each dataset – a tedious and error prone procedure. This is where the knowledge of the RDL format is helpful. Luckily for us, the specification for RDL is documented and available publicly. Using this knowledge, I constructed a quick (and perhaps dirty Smile) PowerShell script to scan through multiple RDL files and extract the T-SQL command text.

To use the script just invoke it from PowerShell command prompt and pass in the root folder path (-RootFolder) where your RDL files are stored. The –Recurse parameter is also useful in case you have many subfolders where RDL files are stored.

Do leave comments on whether this script was useful for you!

This Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment.  THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.  We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded; (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded; and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm.

Comments (4)

  1. Stephen Yale says:

    -RootFolder is the URL to the report server?

  2. Hi Stephen

    Currently this script just looks at RDL files on disk. So the RootFolder argument is exactly that: a folder on disk.



  3. Prabu says:

    Its really good one. it helped to kick start a search tool for SSRS embedded SQL objects.

  4. Ponit says:

    I am not able to get the output returned from script. I placed both rdl files and ps script into same folder and trying to execute the ps script, but it is not returning anything and not even asking to input the parameter. Your help will be much appreciated.

Skip to main content