How to find a row in a table

Question

One of my databases has become closer to the allowed size.

To find out the table containing the maximum data, I used the following query:

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

He returned the table of criminals containing the maximum data.

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

How to achieve this with a query? Are there any tools for this?

Answer 1

This will give you a list of strings by size, just set @table and @idcol accordingly (as written, it will work against the Northwind pattern)

 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) 

Answer 2

A simpler approach for all table sizes is to use a stored procedure on this site . You can modify the select statement of this stored procedure to:

 SELECT * FROM #TempTable Order by dataSize desc 

to sort it by size.

How do you want to clean? Clear the largest row of a specific table? Not sure if I understand the question.

EDIT (response to comment)

Assuming your event log has the same layout as mine (DNN eventlog):

 SELECT LEN(CONVERT(nvarchar(MAX), LogProperties)) AS length FROM EventLog ORDER BY length DESC 

Answer 3

You can also use this to get the size of indexes and keys: (edit: sorry for the text wall, cannot get the format for work)

 WITH table_space_usage ( schema_name, table_name, index_name, used, reserved, ind_rows, tbl_rows ) AS ( SELECT s.Name , o.Name , coalesce(i.Name, 'HEAP') , p.used_page_count * 8 , p.reserved_page_count * 8 , p.row_count , case when i.index_id in ( 0, 1 ) then p.row_count else 0 end FROM sys.dm_db_partition_stats p INNER JOIN sys.objects as o ON o.object_id = p.object_id INNER JOIN sys.schemas as s ON s.schema_id = o.schema_id LEFT OUTER JOIN sys.indexes as i on i.object_id = p.object_id and i.index_id = p.index_id WHERE o.type_desc = 'USER_TABLE' and o.is_ms_shipped = 0 ) SELECT t.schema_name , t.table_name , t.index_name , sum(t.used) as used_in_kb , sum(t.reserved) as reserved_in_kb , case grouping(t.index_name) when 0 then sum(t.ind_rows) else sum(t.tbl_rows) end as rows FROM table_space_usage as t GROUP BY t.schema_name , t.table_name , t.index_name WITH ROLLUP ORDER BY grouping(t.schema_name) , t.schema_name , grouping(t.table_name) , t.table_name , grouping(t.index_name) , t.index_name 

Answer 4

Maybe something like this will work

 delete table where id in ( select top 100 id from table order by datalength(event_text) + length(varchar_column) desc ) 

(since you are dealing with an event table, this is probably the text column that you are looking at ordering, so the sql datalength command is the key here)