Post reply

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

  • 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.

  • December 13, 2006 at 10:38 pm

    #677994

    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

  • December 14, 2006 at 1:39 am

    #678011

    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

  • December 14, 2006 at 8:36 am

    #678098

    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!

  • December 14, 2006 at 1:59 pm

    #678203

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

  • December 14, 2006 at 9:53 pm

    #678253

    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)

  • December 15, 2006 at 8:53 am

    #678367

    Excellent! Thanks to John and Jeff.

  • December 15, 2006 at 4:49 pm

    #678487

    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)

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

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