[SSRS] Get the SQL Query from your RDL via T-SQL

Coworker asked for this.

Basically we wanted to get the queries out of the RDLs on our Reporting Services server.  I saw a post originally that discussed using the tool to pull all the RDLs off of the machine, then parsing them.  Hey, waitasecond....

Immediately found the blog that showed how to use the double-convert to get back the XML.  Once I had that, I started querying the XML to get out what I wanted.  Now why am I getting NULL.....

So, after spending an hour banging my head against the XML and getting back NULL, I went to StackOverflow, whereupon a nice person named Mikael gave me the syntax I was looking for (thanks, Mikael!).  Turns out that you have to ignore the namespace, which means your syntax can get... weird (at least compared to what I normally use).

Overall, though, pleased how it turned out. Please let me know if you have any questions, comments or if there's more stuff I should show.  DataSource coming in a couple weeks; I have the code, just need to integrate it.

(Click on the "View Raw" to get the SQL in a new window.  Thanks!)

[Maintenance] System tables part 2 - list locations of all DB files

I knew that master had to keep track of database files, but I always just used sp_msforeachdb to walk each database and get the data from sysfiles. I like this one a whole lot more.

SELECT name, physical_name
FROM sys.master_files
ORDER BY LEFT(physical_name, 1), name