I didn't invent this (I'm not that good) - but I can't remember where I got it:
SELECT a.name,sum(a.max_length)as Bytes
FROM (
SELECT ST.NAME,SC.MAX_LENGTH
FROM SYS.COLUMNS sc inner join sys.tables st on sc.object_id = st.object_id
UNION ALL
SELECT ST.NAME,SC.MAX_LENGTH
FROM SYS.INDEXES SI INNER JOIN SYS.TABLES ST ON SI.OBJECT_ID = ST.OBJECT_ID
inner join sys.columns sc on sc.object_id = st.object_id
inner join sys.index_columns sic on sc.object_id = sic.object_id
and sc.column_id = sic.column_id ) a
group by a.name
order by a.name
It returns a dataset of table name and row size (approximate)