Hi guys.

The procedure bellow get the data and log size of all databases in current instance.

CREATE PROCEDURE dbo.uspDatabaseSize
AS
declare
@tbl table (Database_Id int, DataUsedMB Float)

insert into @tbl
exec sp_MSforeachdb
'select db_id(''?'') , (SUM(ps.reserved_page_count)*8)/1024 from ?.sys.dm_db_partition_stats ps';
 
WITH DataSize
AS
(
    select
        DB_ID(d.instance_name) Database_Id
        , d.instance_name Database_Name
        , d.cntr_value/1024 DataSizeMB
    from sys.dm_os_performance_counters d
    where d.object_name like '%databases%'
    and d.counter_name like '%Data File(s) Size (KB)%'
),
LogSize AS
(
    select
        DB_ID(p.instance_name) Database_Id
        , p.instance_name Database_Name
        , p.cntr_value/1024 LogSizeMB
    from sys.dm_os_performance_counters p
    where p.object_name like '%databases%'
    and p.counter_name like 'Log File(s) Size (KB)%'
),
LogUsedSize AS
(
    select
        DB_ID(p.instance_name) Database_Id
        , p.instance_name Database_Name
        , p.cntr_value/1024 LogUsedSizeMB
    from sys.dm_os_performance_counters p
    where p.object_name like '%databases%'
    and p.counter_name like 'Log File(s) Used Size (KB)%'
),
LogPercentUsed AS
(
    select
        DB_ID(p.instance_name) Database_Id
        , p.instance_name Database_Name
        , p.cntr_value LogUsedPercent
        , 100 - p.cntr_value LogFreePercent
    from sys.dm_os_performance_counters p
    where p.object_name like '%databases%'
    and p.counter_name like 'Percent Log Used%'
)
select
    ROW_NUMBER() over(order by DataSize.Database_Name) Seq
    , DataSize.Database_Id
    , DataSize.Database_Name
    , DataSize.DataSizeMB
    , t.DataUsedMB
    , (DataSize.DataSizeMB - t.DataUsedMB) DataFreeSizeMB
    , ROUND(((DataSize.DataSizeMB - t.DataUsedMB) / DataSize.DataSizeMB)*100, 2) DataFreePercent
    , LogSize.LogSizeMB
    , LogUsedSize.LogUsedSizeMB
    , (LogSize.LogSizeMB - LogUsedSize.LogUsedSizeMB) LogFreeSizeMB
    , LogPercentUsed.LogUsedPercent
    , LogPercentUsed.LogFreePercent
from DataSize
join LogSize on DataSize.Database_Id = LogSize.Database_Id
join LogUsedSize on  LogSize.Database_Id = LogUsedSize.Database_Id
join LogPercentUsed on LogSize.Database_Id = LogPercentUsed.Database_Id
join @tbl t on DataSize.Database_Id = t.Database_Id
go

Here is a small sample of procedure result: