How to find space and size related details from all tables in a database

Please use below code to pro grammatically find space and size related details of each table in a database. Option 1: This is a custom code which will give more flexibility. Below code will present Rowcounts, Total space consumed and how much of the space used and how much is unused in KB. This result is sorted according to size (larger one at beginning.) So in other word, this code can be used to quickly identify large tables in a database too with some vital details related to space.
SELECT
   t.NAME AS TableName,
   p.rows AS RowCounts,
   SUM(a.total_pages) * 8 AS TotalSpaceKB,
   SUM(a.used_pages) * 8 AS UsedSpaceKB,
   (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM
   sys.tables t
INNER JOIN      
   sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
   sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
   sys.allocation_units a ON p.partition_id = a.container_id
WHERE
   t.NAME NOT LIKE 'dt%'
   AND t.is_ms_shipped = 0
   AND i.OBJECT_ID > 255
GROUP BY
   t.Name, p.Rows
ORDER BY
   TotalSpaceKB desc
 
Example output:

Option 2:
If you do not need to sort the result, you may use below code which even gives size of index. However as this is nothing but running sp_spaceused on all tables of the database separately, this result is not easily organisable when you are dealing with a huge number of databases.
sp_msforeachtable 'EXEC sp_spaceused [?]'
GO

Example output: