Calculate row length of table(s)

  • kns

    Grasshopper

    Points: 18

    Comments posted to this topic are about the item Calculate row length of table(s)

  • vm.bharathiraja

    SSC Rookie

    Points: 32

    What is row length of table?

    is that mean the size of the row?

  • sscnn

    Valued Member

    Points: 70

    A bit of modifications to take care of schemas

    SET quoted_identifier ON

    GO

    CREATE PROCEDURE [dbo].[usp_find_row_lenght]

    (

    @schema_name VARCHAR(100)=''

    ,@table_name VARCHAR(100)=''

    )

    AS

    BEGIN

    SET nocount ON

    IF @table_name=''

    OR @schema_name=''

    BEGIN

    SELECT

    ss.name AS schema_name,

    o.name AS table_name,

    SUM(c.length) AS row_lenght

    FROM

    sysobjects o

    INNER JOIN syscolumns c

    ON o.id=c.id

    INNER JOIN sys.objects so

    ON o.id=so.object_id

    INNER JOIN sys.schemas ss

    ON so.schema_id=ss.schema_id

    WHERE (o.xtype='u')

    AND (o.type='u')

    GROUP BY

    o.name,

    ss.name

    ORDER BY

    ss.name,

    o.name

    END

    ELSE

    BEGIN

    SELECT

    ss.name AS schema_name,

    o.name AS table_name,

    SUM(c.length) AS row_lenght

    FROM

    sysobjects o

    INNER JOIN syscolumns c

    ON o.id=c.id

    INNER JOIN sys.objects so

    ON o.id=so.object_id

    INNER JOIN sys.schemas ss

    ON so.schema_id=ss.schema_id

    WHERE (o.xtype='u')

    AND (o.type='u')

    AND o.name=@table_name

    AND ss.name=@schema_name

    GROUP BY

    o.name,

    ss.name

    ORDER BY

    ss.name,

    o.name

    END

    END

    GO

  • sscnn

    Valued Member

    Points: 70

    vm.bharathiraja (8/25/2009)


    What is row length of table?

    is that mean the size of the row?

    It's sum of the lenght of the fields in a row in bytes. It's not however the actual size of each of row, as that depends on other things besides the filed lenght.

  • Preethiviraj Kulasingham

    Mr or Mrs. 500

    Points: 523

    This script does not consider the row overhead involved over variable columns, nullable columns and bit columns. I believe ( not tested though) about calculated columns were also not considered here.

    Other than these issues, this script will give the sum of all bytes needs for the row itself.

    Cheers,
    Prithiviraj Kulasingham

    Plan to Test your Plan!

Viewing 5 posts - 1 through 5 (of 5 total)

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