Nothing too clever here. In fact, quite the opposite. I’m posting so I can find this again in the future..!

I recently needed to take some data about a large number of databases on an instance and generate a report for people that probably wouldn’t read said report.

As is becoming a trait recently, dynamic SQL to the rescue.

Iterate all the online databases and run a DBCC dbinfo against them. The ‘WITH tableresults’ forces the output to the console (in my case a file) so it can be used. Simples.

DECLARE @SQL NVARCHAR(max)

SELECT @SQL = 
COALESCE(@SQL,'') +
'USE ' + QUOTENAME(Name) + '
DBCC dbinfo ('''') WITH tableresults
'
FROM sys.databases WHERE state_desc = 'ONLINE'

EXECUTE(@SQL)

Usual caveats and lack of warranty applies 😉

more to follow…