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)

Share

Share

Rate

4.75 (4)