Step 1:

Open the SQL Server Management Studio

Step 2:

Click on New Query

Step 3:

enter the follow command into the query and execute:

select * from sys.master_files

Step 4:

Open a new query with the command and execute again. Attention, the Iteration at the third row "while @ iteration <= 6" has to be adjusted, depend on the database_id on the step 3. There you can see we have 6 database ID's. If you have more, please adjust this to the same numbers of ID's.

DECLARE @Iteration INT

DECLARE @Total_size_sum DECIMAL(8, 2)

SET @Iteration = 1

SET @Total_size_sum = 0

IF OBJECT_ID ('tempdb..#temp_sizes_table', 'U') is not null drop table #temp_sizes_table

WHILE @Iteration <= 6

Begin

 SELECT database_name = db_name(@Iteration),

 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))

 into #temp_sizes_table

 from sys.master_files with(nowait)

 WHERE database_id = @Iteration -- füraktuelle DB

 Group by database_id

 SET @Iteration = @Iteration + 1

  SET @Total_size_sum = @Total_size_sum + (SELECT total_size_mb from #temp_sizes_table)

 -- Individual databases can be displayed if required

 --select * from #temp_sizes_table

 IF OBJECT_ID ('tempdb..#temp_sizes_table', 'U') is not null drop table #temp_sizes_table

 End

SELECT sum((size * 8./1024)) as Total_size_sum_mb

from sys.master_files

Step 5:

Now you can see the used size on all database.