Please visit our sponsor
<![CDATA[UNKNOWN
--**************************************
-- Name: Discovering a table size
-- Description:This procedure help you to discover what will be the table size with the records, so you can plan the database size.
-- By: Dennes Torres de Oliveira
--
--
-- Inputs:The input parameters are the table name and the expected number of records. The numbers of records must be entered with ".00" like 5000.00
--
-- Returns:The result is the size of a record, the number of records per page, total of pages, total of extensions and size of the table (in kb) when the table has the specified number of records
--
--Assumes:You will need to create this procedure in the master database of the server, so you can use it if all of your databases.
--
--Side Effects:This procedure still doesn't calc the size of index and fill factors of the table. It will be done in a later version.
--This code is copyrighted and has limited warranties.
--Please see http://www.Planet-Source-Code.com/xq/ASP/txtCodeId.260/lngWId.5/qx/vb/scripts/ShowCode.htm
--for details.
--**************************************
create procedure sp_calcsize @tab varchar(30),@numreg numeric(18,2) as
/* Copyright by Bufalo Informatica
http://www.bufaloinfo.com.br
this lines cann't be removed */
declare @tottam int
declare @colfix int
declare @colvar int
declare @tamreg int
declare @regpag numeric(18,2)
declare @numpag int
declare @numext int
select @tottam=sum(length) from syscolumns a,sysobjects b where a.id=b.id
and b.name=@tab
select @colfix=count(*) from syscolumns a,sysobjects b,systypes c where
a.id=b.id and c.xusertype=a.xtype and b.name=@tab and variable<>1
select @colvar=count(*) from syscolumns a,sysobjects b,systypes c where
a.id=b.id and c.xusertype=a.xtype and b.name=@tab and variable=1
select @tamreg=@tottam + (@colvar*2) + convert(int,2+((@colfix+7)/8)) + 2 +
4
select @regpag=floor(8096.00/(@tamreg+2))
select @numpag=ceiling(@numreg/@regpag)
select @numext=ceiling(@numpag/8.00)
select 'tamanho do registro'=@tamreg,'Registros por página'=@regpag,
'Número de páginas'=@numpag,'Número de extensões'=@numext,'Espaço
total'=@numext*64
]]>