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…