Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.

Finding all the tables in all the databases on a MS SQL Server

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?

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">