SQL Server: sp_MSforeachdb into a single result set

Question

I am going to archive to get a query that retrieves some data from all databases and returns one result set.

Now I have the following:

DECLARE @command varchar(4000) SELECT @command = ' USE [?] SELECT database_name = DB_NAME(database_id) , log_size_mb = CAST(SUM(CASE WHEN type_desc = "LOG" THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , row_size_mb = CAST(SUM(CASE WHEN type_desc = "ROWS" THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) , Kostenstelle = (select value from sys.extended_properties WHERE name = "Kostenstelle") FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID() GROUP BY database_id ' EXEC sp_MSforeachdb @command 

The code above will return to multiple result sets.

The result should look like this:

 |database_name|log_size_mb|row_size_mb|Kostenstelle| +-------------+-----------+-----------+------------+ |demoA | 12| 10| xxxx| |demoB | 52| 12| xxxx| 

Answer 1

Got a response from a colleague - 1000 thanks

 DECLARE @command varchar(4000) SELECT @command = ' USE [?] SELECT database_name = CAST(DB_NAME(database_id) AS VARCHAR(50)) , log_size_mb = CAST(SUM(CASE WHEN type_desc = "LOG" THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , row_size_mb = CAST(SUM(CASE WHEN type_desc = "ROWS" THEN size END) * 8. / 1024 AS DECIMAL(8,2)) , total_size_mb = CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) , Kostenstelle = CAST((select value from sys.extended_properties WHERE name = "Kostenstelle") AS VARCHAR(10)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID() GROUP BY database_id ' DECLARE @DatabasesKst TABLE ( database_name VARCHAR(50), log_size_mb DECIMAL(8,2), row_size_mb DECIMAL(8,2), total_size DECIMAL(8,2), Kostenstelle VARCHAR(100) ) INSERT INTO @DatabasesKst EXEC sp_MSforeachdb @command select * from @DatabasesKst 

Answer 2

You can create a table in some db and save the results in this table if you add an INSERT to your query:

 DECLARE @command varchar(4000) SELECT @command = ' USE [?] INSERT INTO <DbName>.<SchemaName>.<TableName> SELECT database_name = DB_NAME(database_id) ...