Technical Article

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

Selectl1o.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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating