--**************************************
--
-- Name: Discovering a table size
-- Description:This procedure help you t
-- o discover what will be the table size w
-- ith the records, so you can plan the dat
-- abase size.
-- By: Dennes Torres de Oliveira
--
-- Inputs:The input parameters are the t
-- able name and the expected number of rec
-- ords. The numbers of records must be ent
-- ered with ".00" like 5000.00
--
-- Returns:The result is the size of a r
-- ecord, the number of records per page, t
-- otal of pages, total of extensions and s
-- ize 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 doe
-- sn't calc the size of index and fill fac
-- tors 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/txtCod
-- eId.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
|
