如何在表中找到行大小 [英] how to find rowsize in table

问题描述

我的一个数据库已接近允许的大小.

为了找出包含最大数据的表，我使用了以下查询:

exec sp_MSforeachtable @command1="打印 '?'exec sp_spaceused '?'"

它返回了包含最大数据的罪魁祸首表.

下一步，我想根据大小清理行.为此，我想根据大小对行进行排序.

如何使用查询来实现这一点?有什么工具可以做到这一点吗?

解决方案

这将为您提供按大小排列的行列表，只需相应地设置 @table 和 @idcol(如所写，它将针对 Northwind 示例运行)

声明@table varchar(20)声明@idcol varchar(10)声明@sql varchar(1000)设置@table = '员工'设置@idcol = 'EmployeeId'set @sql = 'select' + @idcol +' , (0'选择@sql = @sql + ' + isnull(datalength(' + name + '), 1)'从 syscolumns where id = object_id(@table)将 @sql = @sql + ') 设置为来自 ' + @table + ' order by rowsize desc' 的 rowsize执行(@sql)

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?

解决方案

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)

这篇关于如何在表中找到行大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！