October 13, 2007 at 3:17 pm
Comments posted to this topic are about the item Calculate row length of table(s)
August 25, 2009 at 3:23 am
What is row length of table?
is that mean the size of the row?
November 22, 2009 at 12:06 pm
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
November 22, 2009 at 12:08 pm
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.
June 23, 2011 at 9:36 am
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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply