Technical Article

Calculate row length of table(s)

,

This procedure will be useful for developers/ database designers / DBAs to find the rowlength instantly.
To find the row length of a user table, just copy this script into Query analyser and pass the table name for which you want to find the rowlength. @TabName is the table name parameter, if we donot provide this while execution the procedure returns table length for all the user tables in the logged in database.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

Create Procedure FindRowLength ( @TabName VarChar(100) = '' )
As
Begin
set nocount on
If @TabName = ''
Begin
SELECTo.name, Sum( C.length ) As rowLength from 
sysobjects o 
INNER JOIN
        sysColumns C 
ON o.id = C.id
WHERE(o.xtype = 'u') AND (o.type = 'u') 
Group by o.name
ORDER BY o.name
End
Else
Begin
SELECTo.name, Sum( C.length ) As rowLength from 
sysobjects o 
INNER JOIN
        sysColumns C 
ON o.id = C.id
WHERE(o.xtype = 'u') AND (o.type = 'u')  and o.name = @TabName
Group by o.name
ORDER BY o.name
End
End

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

execute FindRowLength

Rate

4.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

4.75 (4)

You rated this post out of 5. Change rating