Row Size of each row in each table - Urgent

Hi. I am trying to get a row count of each row of each table in the database. Is that possible? Using a SP or UDFS? I dont want the column size of each table but the total datasize of each row.

So for example if I have 5 rows each in 3 tables I need a query that will return 15 rows with the size of each row(size of all coumn data summed together). Thanks.You should be able to make use of theDATALENGTH function for this information.|||

You can use sp_spaceused @.tableName to displays the number of rows, disk space reserved, and disk space used by a table in the current database. There is no direct to retrieve exact rowsize for each row (e.g. rows with VARCHAR/TEXT data), so we can estimate the average rowsize for each table. Here is a quick sample:

CREATE PROCEDURE sp_GetAvgRowSize @.TableName sysname=null
AS
DECLARE @.TotalRowSize decimal(8,2),@.RowCount int,@.TblID int,@.TblCnt int

IF @.TableName is not null
BEGIN
select @.TotalRowSize=sum(dpages),@.RowCount=max(rowcnt)
from sysindexes
where id=object_id(@.TableName)
and indid<2

select @.TotalRowSize=isnull(sum(used),0)+@.TotalRowSize
from sysindexes
where id=object_id(@.TableName)
and indid=255
SELECT @.TotalRowSize=@.TotalRowSize*8
SELECT @.TableName as 'TableName',@.RowCount as 'RowCount',
convert(varchar(100),convert(decimal(8,2),(@.TotalRowSize/@.RowCount)))+'KB' as 'AvgRowSize'
END

ELSE
BEGIN
SELECT name,id into #tbl_UserTables from sysobjects where xtype='U'
SELECT @.TblID=max(id),@.TblCnt=count(*) from #tbl_UserTables
WHILE @.TblCnt >0
BEGIN
select @.TotalRowSize=sum(dpages),@.RowCount=max(rowcnt)
from sysindexes
whereid=@.TblID
and indid<2

select @.TotalRowSize=isnull(sum(used),0)+@.TotalRowSize
from sysindexes
whereid=@.TblID
and indid=255
SELECT @.TotalRowSize= @.TotalRowSize*8

SELECT object_name(@.TblID) as 'TableName',@.RowCount as 'RowCount',
'AvgRowSize'=CASE @.RowCount
WHEN 0 THEN 'No Rows in the table'
ELSE convert(varchar(100),convert(decimal(8,2),(@.TotalRowSize/@.RowCount)))+'KB'
END
DELETE FROM #tbl_UserTables WHEREID=@.TblID
SELECT @.TblID=max(id),@.TblCnt=count(*) from #tbl_UserTables
END
END
go

|||

Iori_Jay:

There is no direct to retrieve exact rowsize for each row (e.g. rows with VARCHAR/TEXT data)

DATALENGTH will tell you the number of bytes in a column, even varchar and text columns.|||Thanks for your remind Sorry for misleading, actually I mean we can not get exact rowsize without sum columns, thanks again for your kindly remind|||Thank you all of you. I will try it and let you guys know if I run into any trouble.|||

lori_jay is correct, there is no way that I know of to get the exact amount of space a row is taking on disk.

tmorton is also correct in that Datalength will tell you the amount of bytes that a field contains, but it won't tell you about the overhead/pointer space/space wasted per field, row overhead, or wasted space in a page.

If all you really need is the number of bytes that is contained withing a row, then add the datalengths. If you really wanted to know how much disk space a row consumes, then it's *VERY* inaccurate, and lori_jay's approach is better, although it doesn't say on a per-row basis.