Today I was assigned a task to query multiple instances and gather space usage details on all databases. First I thought it would be quite easy task to do, but as my final goal was to summarize the statistics per database and not per database file, I had to modify the standard query (which turned out to be one of the most popular suggestions Google and Bing provided). Anyway, if you want to query multiple instances and gather space usage statistics on database and file level, there is the script:
declare @temp table (
insert into @temp
exec sp_MSforeachdb ‘
CAST(SERVERPROPERTY(”MachineName”) AS VARCHAR(100)) + ”\” + CAST(SERVERPROPERTY(”InstanceName”) AS VARCHAR(100))
,DB_NAME() AS DatabaseName
select * from @temp
The @temp table is used so I can easily copy the results to Excel for example as sp_MSforeachdb returns separate blocks in the result screen. Also based on that table you can summarize database size info if you do not feel confident in doing it with excel.
The script can be run on an instance level or on a Registered Server Group level (for SQL Server 2008 – querying multiple instances).