Discovering a table size

				
--**************************************
-- 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/vb/scripts/ShowCode.asp?txtCodeId=260&lngWId=5--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