space-per-column.sql

create table #space ([table] nvarchar(255), [column] nvarchar(255) not null, [bytes] bigint null);

declare @sql varchar(max) = ''
declare @tablepattern as varchar(255) = '%'
declare @exclusionpattern as varchar(255) = ''

select @sql = @sql + 'insert into #space select ''' + t.name + ''', ''' + c.name + ''', sum(datalength([' + c.name + '])) as bytes from [' + t.name + '];' 
from sys.columns c
inner join sys.tables t on c.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id
where c.is_computed = 0 and s.name = 'dbo' and t.name like @tablepattern and t.name not like @exclusionpattern;

exec (@sql)

select [table], format(sum([bytes]), '#,#') as [size]
from #space
group by [table]
order by sum(bytes) desc;

with cte ([table], [column], [bytes], [bytesInTable]) as (
	select [table], [column], [bytes], (select sum([bytes]) from [#space] s where s.[table] = [table]) as [bytesInTable]
	from [#space]
)
select [table], [column], format([bytes], '#,#') as [size], format([bytes] * 1. / [bytesInTable], 'p')
from cte
order by [bytesInTable] desc, [table] asc, [bytes] desc;
;

drop table #space