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
	SELECT	o.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
	SELECT	o.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)