Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate row length of table(s) Expand / Collapse
Author
Message
Posted Saturday, October 13, 2007 3:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 11, 2005 9:48 PM
Points: 2, Visits: 1
Comments posted to this topic are about the item Calculate row length of table(s)


Post #410396
Posted Tuesday, August 25, 2009 3:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 28, 2009 6:17 AM
Points: 2, Visits: 12
What is row length of table?
is that mean the size of the row?
Post #776515
Posted Sunday, November 22, 2009 12:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 9, 2011 1:04 AM
Points: 4, Visits: 65
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

Post #823000
Posted Sunday, November 22, 2009 12:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 9, 2011 1:04 AM
Points: 4, Visits: 65
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.
Post #823001
Posted Thursday, June 23, 2011 9:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 16, 2013 4:42 AM
Points: 19, Visits: 86
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!
Post #1130574
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse