I needed to deconstruct some one else’s report for MS reporting services in order to add some much needed detail to part of the report. I got the RDL, and RDS files (report definition, and data source description files) and extracted the SQL. Looking at the SQL code there was a reference to a table I did not recognize. The problem was that on the server that this data resided there were over 300 databases. I did not want to search all 300 databases for one table. so with some fiddling around I came up with this solution:
This gave me the database name followed by a list of all the tables in that database. In my case sending the results to a test file and doing a “find” on the table name I was looking for was needed as the results were quite big.
There are lots of great uses for this command, for instance:
Will give you the space used and unallocated for each of the databases on the server.
The command: sp_msforeachdb appears to be undocumented, which means of course that care and discretion should be used when implementing solutions with this command, as it may not appear in a future version of the program.
Have you heard of this before? What are your favorite uses of this command?