Part I: Table Size Estimation

,

This script will provide the first four values required for estimating the size of a database table.  It will satisfy steps one and two in MS estimation process for the amount of space required to store the data in a MS SQL Server v7.0 or v2000 table:
1    Specify the number of rows present in the table:
    Number of rows in the table = Num_Rows

2    If there are fixed-length and variable-length columns in the table definition, calculate the space that each of these groups of columns occupies within the data row. The size of a column depends on the data type and length specification. For more information, see Data Types.
    Number of columns = Num_Cols
    Sum of bytes in all fixed-length columns = Fixed_Data_Size
    Number of variable-length columns = Num_Variable_Cols
    Maximum size of all variable-length columns = Max_Var_Size

Select	l1o.name,

        (SELECT i.rows
	 FROM 	sysindexes i
	 WHERE 	i.id = l1o.id 
	   AND  i.indid In (0,1)) AS Row_Count,

	(SELECT	 Count(*)
         FROM	 sysColumns c
	 WHERE   l1o.id = c.id )as Num_Cols,

	(SELECT  Sum(fdc.length) 
 	 FROM	 sysColumns fdc
  		,systypes   fdt        
 	  WHERE	 fdc.xtype     = fdt.xtype
	    AND	 fdt.variable  = 0
	    AND	 l1o.id        = fdc.id
	  GROUP BY l1o.id) as Fixed_Data_Size,

	(SELECT	 Count(*)
         FROM	 sysColumns vdc
         	,systypes   vdt
	 WHERE   l1o.id = vdc.id
           AND   vdc.xtype     = vdt.xtype
           AND   vdt.variable  = 1)as Num_Variable_Col,

	ISNULL(
    	(SELECT  Sum(vdc.length) 
 	 FROM	 sysColumns vdc
  		,systypes   vdt        
 	  WHERE	 vdc.xtype     = vdt.xtype
	    AND	 vdt.variable  = 1
	    AND	 l1o.id        = vdc.id
	  GROUP BY l1o.id),0) as Max_Var_Size

FROM 	sysobjects l1o

WHERE	(l1o.xtype = 'u') 
  AND	(l1o.type = 'u')

Order by l1o.name

Rate

Share

Share

Rate