T-SQL: Which schema is consuming more space?

Imagine that you have separate schemas in your multi-tenant data architecture and you want to know who is consuming more space. How can you do it?

Just run the following query:

    SELECT  SCHEMA_NAME(obj.schema_id) AS 'Schema',
            SUM(stats.reserved_page_count) * 8.0 / 1024 AS SizeInMB
    FROM    sys.dm_db_partition_stats stats
        JOIN    sys.indexes idx ON idx.object_id = stats.object_id AND idx.index_id = stats.index_id
        JOIN    sys.objects obj ON idx.object_id = obj.object_id
    WHERE   obj.Type = 'U'
    GROUP BY    obj.schema_id
    ORDER BY    SizeInMB DESC

Hope this helps.