How to analyze tables for size in a single DB using SQL Server?

Question

I have a DB called Johnson containing 50 tables. The team that uses this DB has asked if I can provide them with a size estimate for each table

How can I display a list of all the tables within the DB and their respective size in GB or MB using SQL Server?

Answer 1

You could use SSMS to display the size of biggest 1000 tables thus:

Answer 2

SET NOCOUNT ON 

DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' 

SELECT *
FROM   #t

-- # of rows.
SELECT SUM(CAST([rows] AS int)) AS [rows]
FROM   #t

DROP TABLE #t 

When ran In a database context give results something like this.. Northwind Database