Skip to content

Instantly share code, notes, and snippets.

@jtheisen
Last active October 12, 2018 15:29
Show Gist options
  • Save jtheisen/c8d4a3af32a4e9b987b0a5b00acc2b12 to your computer and use it in GitHub Desktop.
Save jtheisen/c8d4a3af32a4e9b987b0a5b00acc2b12 to your computer and use it in GitHub Desktop.
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment