;with cte as (

(select t.name as TableName,i.name as IndexName,

sum(row_count)as row_count,

SUM (s.used_page_count) as used_pages_count

FROM sys.dm_db_partition_stats  AS s

JOIN sys.tables AS t ON s.object_id = t.object_id

JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id

group by t.name, i.name)

union all

(select t.name as TableName,i.name as IndexName,

sum(row_count)as row_count,

SUM (s.used_page_count) as used_pages_count

FROM sys.dm_db_partition_stats  AS s

JOIN sys.views AS t ON s.object_id = t.object_id

JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id

group by t.name, i.name)

)

select

    cte.TableName,

    cte.IndexName,

    cast((cte.used_pages_count * 8.)/1024 as decimal(10,3)) as TableSizeInMB

from cte

order by 1 desc;

go