with RowQtys as (
    SELECT 
         TableName = '[' + s.name + '].[' + o.name + ']'
        ,RowQty = sum(p.rows)  
     
    FROM
        sys.objects o 

        inner join sys.schemas s
            on o.schema_id = s.schema_id

        inner join sys.partitions p 
            on p.object_id = o.object_id
            and o.type = 'U'

        LEFT JOIN  sys.allocation_units a 
            ON p.partition_id = a.container_id 

    WHERE 1=1
        -- 0 = heap table, 1 = table with clustered index
        and p.index_id in (0, 1) 

        and a.type = 1 -- row-data only , not LOB
        and p.rows is not null

    group by
        o.name, s.name
    )
,ColumnSizes as (
    select distinct
         TableName = '[' + s.name + '].[' + o.name + ']'
        ,ColumnSize = 1 + case t.name
            when 'bigint'           then 8
            when 'int'              then 4
            when 'smallint'         then 2
            when 'tinyint'          then 1
            when 'bit'              then 1
            when 'numeric'          then 19
            when 'money'            then 8
            when 'float'            then 8
            when 'real'             then 8
            when 'datetime'         then 8
            when 'nchar'            then 2 * c.max_length
            when 'nvarchar'         then 2 * c.max_length
            when 'ntext'            then 2 * c.max_length
            when 'binary'           then 510
            when 'varbinary'        then 510
            when 'uniqueidentifier' then 16
            when 'varchar'          then c.max_length + 2
            end

    from 

        sys.objects o

        inner join sys.columns c 
            on o.object_id = c.object_id

        inner join sys.types t 
            on t.system_type_id = c.system_type_id

        inner join sys.schemas s
            on o.schema_id = s.schema_id

    where 1=1 
        and o.type = 'u'
        and t.name not in ('sysname')
        and o.name not in ('dtproperties')
        and o.name not like 'sys%'
        and o.name not like 'z[_]DeadCode[_]%'
    )
select 
     cs.TableName
    ,RowSize = sum(cs.ColumnSize) + 6
    ,rc.RowQty
    ,TableSize = (sum(cs.ColumnSize) + 6) * rc.RowQty
from 
    ColumnSizes cs
    left join RowQtys rc
        on cs.TableName = rc.TableName
group by 
    cs.TableName, rc.RowQty
ORDER BY 
    cs.TableName