How much space does each table take up?

In the DevOps world space matters, especially when you are using PAAS databases in the cloud such as Azure SQL Database (aka SQL Azure) where there can be tight space limits per database. One useful way to keep track of how much space each table (and accompanying indexes) take up is to use a query like the following:

 

select t.[name], ps.* from sys.dm_db_index_physical_stats(DB_ID(), 0, -1, 0, null) as ps

left outer join sys.tables as t on ps.object_id = t.object_id

 For more information about sys.dm_db_index_physical_stats including the performance impact of using it check out an excellent article by Paul Randal at https://www.sqlskills.com/blogs/paul/inside-sys-dm_db_index_physical_stats/.