Get size of all tables in a database

SELECT
        T.NAME AS TableName,
        S.Name AS SchemaName,
        P.rows AS RowCounts,
        SUM(AU.total_pages) * 8 / 1024 AS TotalSpaceMB,
        SUM(AU.used_pages) * 8 / 1024 AS UsedSpaceMB
FROM
        syS.tables T
        JOIN sys.indexes I ON T.object_id = I.object_id
        JOIN sys.partitions P ON I.object_id = P.object_id AND I.index_id = P.index_id
        JOIN sys.allocation_units AU ON P.partition_id = AU.container_id
        JOIN sys.schemas S ON T.schema_id = S.schema_id
GROUP BY
        T.Name, S.Name, P.Rows
ORDER BY
        TotalSpaceMB DESC

Is this useful?