how to find rowsize in table

Question

One of my DBs have grown closer to permitted size.

Inorder to find out the table containing the max data, i used the following query:

exec sp_MSforeachtable @command1="print '?' exec sp_spaceused '?'"

It returned the culprit table comprising the max data.

As a next step, i want to cleanup the rows based on the size. For this, i would like to order the rows based on size.

How to achieve this using a query? Are there any tools to do this?

Answer 1

This will give you a list of rows by size, just set @table and @idcol accordingly (as written it'll run against the Northwind sample)

declare @table varchar(20)
declare @idcol varchar(10)
declare @sql varchar(1000)

set @table = 'Employees'
set @idcol = 'EmployeeId'
set @sql = 'select ' + @idcol +' , (0'

select @sql = @sql + ' + isnull(datalength(' + name + '), 1)' 
    from syscolumns where id = object_id(@table)
set @sql = @sql + ') as rowsize from ' + @table + ' order by rowsize desc'

exec (@sql)