How to count the number of bytes in in each row?

Question

Post reply

How to count the number of bytes in in each row?

SQL ORACLE

One Orange Chip

Points: 27807
More actions
December 13, 2006 at 9:32 pm

#118242

My question is how to count the number of bytes defined in each row in a table using script.
Many thanks in advance.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

MohammedU SSCoach Points: 19644 More actions · Answer 1

SELECT table_name, sum(COL_LENGTH(table_name, column_name)) AS ColumnLength

FROM INFORMATION_SCHEMA.columns

group by table_name

MohammedU
Microsoft SQL Server MVP

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

This won't work if some of your data is (n)varchar or text. If you want to count the size of these rows individually, you'll need to use the datalength function. Be careful of nulls, though: DATALENGTH(NULL) is NULL. If it's just an average you're after, you can use sp_MStablespace. These two links might be helpful.

http://www.sqlservercentral.com/columnists/aLohia/findingtablespace.asp

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=135

John

Lowell SSC Guru Points: 323504 More actions · Answer 3

i have this snippet where i was looking for the defined row size for each table:

this might help as well:

create table ##tmp (TableName varchar(40),DefinedRowSize int)

sp_msforeachtable 'INSERT INTO ##TMP Select ''?'' As TableName, SUM(C.Length) as Length from dbo.SysColumns C where C.id = object_id(''?'') '

select * from ##tmp order by DefinedRowSize desc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

john-132685 SSCertifiable Points: 6084 More actions · Answer 4

What's wrong with summing the length field in the syscolumns table? (where id = object_id( tablename ) )

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 5

Nicely done... very nice, indeed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Carlos Urbina SSCommitted Points: 1850 More actions · Answer 6

Carlos Urbina

SSCommitted

Points: 1850

December 15, 2006 at 8:53 am

#678367

Excellent! Thanks to John and Jeff.

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 7

I appreciate the compliment but I didn't do anything... my comment was actually directed to Mohammed's fine and easy solution.

John is correct... using the system tables would certainly work, as well

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)