Решение:

declare @RowCount int, @tablename varchar(100)
declare @Tables table (
PK int IDENTITY(1,1),
tablename varchar(100),
processed bit
)
INSERT into @Tables (tablename)
SELECT TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE' and TABLE_NAME not like 'dt%' order by TABLE_NAME asc
declare @Space table (
name varchar(100),
rows nvarchar(100),
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused varchar(100)
)
WHILE ((select COUNT(*) from @Tables)<> 0)
BEGIN
declare @ft nvarchar(100)
set @ft=(select top 1 tableName from @Tables)
insert into @Space exec sp_spaceused @ft
delete from @Tables where tablename=@ft

END

update @Space set data = replace(data, ' KB', '')
update @Space set data = convert(int, data)/1000
update @Space set data = data + ' MB'
update @Space set reserved = replace(reserved, ' KB', '')
update @Space set reserved = convert(int, reserved)/1000
update @Space set reserved = reserved + ' MB'

select * from @Space order by convert(int, replace(data, ' MB', '')) desc
нагуглил эту строчку
exec sp_spaceused 'tablename'