Which Table is Consuming more space

--How to find out the size of all tables

select st.name,sp.rows, su.total_pages*8 AS TotalSpaceinKB ,su.used_pages*8 AS UsedSpaceinKB ,
(total_pages-used_pages)*8 as UNusedspaceinkb
from sys.tables st
inner join sys.partitions sp on st.object_id=sp.object_id
inner join sys.allocation_units su on su.container_id=sp.partition_id

--How to find out which table is consuming more space

select st.name ,sp.rows, su.total_pages*8 AS TotalSpaceinKB ,(su.used_pages)*8 AS UsedSpaceinKB ,
(total_pages-used_pages)*8 as UNusedspaceinkb
from sys.tables st
inner join  sys.partitions sp on st.object_id=sp.object_id
inner join  sys.allocation_units su on su.container_id=sp.partition_id where (su.used_pages)in
(select max(su.used_pages) AS UsedSpaceinKB
from sys.tables st
inner join  sys.partitions sp on st.object_id=sp.object_id
inner join  sys.allocation_units su on su.container_id=sp.partition_id
)