Modified From:
if exists (select * from sysobjects where id = object_id(N'[dbo].[calcspace]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[calcspace]
GO
create procedure CalcSpace
/************************************************************************/
/* Stored Procedure: CalcSpace */
/* Creation Date: 1999-04-11 */
/* Copyright: - */
/* Written by: Sharon Dooley */
/* */
/* Purpose: */
/* A procedure to estimate the disk space requirements of a table. */
/* Refer to Books OnLine topic "Estimating the size of a table..." */
/* for a detailed description */
/* */
/* Input Parameters: */
/* VARCHAR(30) Name of table to estimate */
/* INT Number of rows in the table */
/* */
/* Output Parameters: */
/* - */
/* */
/* Return Status: */
/* - */
/* */
/* Usage: */
/* EXEC CalcSpace 'MyTable', 10000 */
/* */
/* Other info: */
/* The is a direct copy from the CalcSpace stored procedure made by*/
/* Sharon Dooley, 1999-04-11. The only change is the added */
/* documentation header and a small bug fix mentioned below. */
/* */
/* Updates: */
/* Date Author Purpose */
/* 2000-07-04 Magnus Andersson Changed from tinyint */
/* to int to prevent overflow */
/* scenario. Added documentation. */
/* */
/************************************************************************/
( varchar(30)=null-- name of table to estimate
)
as
declare @msg varchar(120)
declare int --number of rows in the table
set = (select rowcnt from (SELECT object_name(id) as tablename ,rowcnt
FROM sysindexes with (nolock)
WHERE indid IN (1,0) AND OBJECTPROPERTY(id, 'IsUserTable') = 1) a where tablename = )
-- Give usage statement if is null
if = null or = 0
begin
print 'Usage is:'
print ' calcspace table_name, no_of_rows'
print 'where table_name is the name of the table,'
print ' no_of_rows is the number of rows in the table,'
print ' '
return
end
declare int,
int,
l int,
int,
int,
ize int,
int,
int,
ytes int,
eg real,
bytes real,
int,
int,
int,
age int,
int,
cols int,
int,
ey_cols int,
ze int,
map int,
ize int,
int,
_page int,
int,
l_0 int,
l_1 int,
l_x int,
l_y int,
s int,
_size_in_bytes int,
ols int,
int,
y_cols int,
e int,
ap int,
ze int,
ze int,
er_page int,
int,
page int,
_per_page int,
_0 int,
_1 int,
_x int,
_y int,
int,
dex_size int,
ustered_index_pages int,
s_per_page int,
int
-- initialize variables
select =0,
=0,
l =0,
=0,
=0,
ize =0,
=0,
=0,
ytes =0,
eg =0,
bytes =0,
=0,
=0,
=0,
cols =0,
=0,
ey_cols =0,
ze =0,
map =0,
ize =0,
=0,
_page =0,
=0,
l_0 =0,
l_1 =0,
s =0,
_size_in_bytes =0,
ols =0,
=0,
y_cols =0,
e =0,
ap =0,
ze =0,
ze =0,
er_page =0,
=0,
page =0,
_per_page =0,
_0 =0,
_1 =0,
_x =0,
_y =0,
=0,
dex_size =0,
ustered_index_pages =0,
s_per_page =0,
=0
set nocount on
--*********************************************
-- MAKE SURE TABLE EXISTS
--*********************************************
select = sysstat,
= id
from sysobjects where name =
if & 7 not in (1,3)
begin
select @msg = 'I can''t find the table
print @msg
return
end
--*********************************************
-- ESTIMATE SIZE OF TABLE
--*********************************************
-- get total number and total size of fixed-length columns
select = count(name),
= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=0
)
if = 0 is null. change to 0
select =0
-- get total number and total maximum size of variable-length columns
select l=count(name),
= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=1
)
if l= 0 is null. change to 0
select =0
-- get portion of the row used to manage column nullability
select =2+((+7)/8)
-- determine space needed to store variable-length columns
-- this assumes all variable length columns will be 100% full
if l = 0
select ize=0
else
select ize = 2 + (l *2 )+
-- get row size
select = +
ize +
+ 4 -- 4 represents the data row header
-- get number of rows per page
select = (8096) / (+2)
-- If a clustered index is to be created on the table,
-- calculate the number of reserved free rows per page,
-- based on the fill factor specified.
-- If no clustered index is to be created, specify Fill_Factor as 100.
select = 100 -- initialize it to the maximum
select age = 0 --initialize to no free rows/page
select =OrigFillFactor
from sysindexes
where id = and indid=1 -- indid of 1 means the index is clustered
if <>0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the data pages in this section
select age=8096 * ((
-- get number of pages needed to store all rows
select = ceiling(convert(dec,) / (-age))
-- get storage needed for table data
select =8192*
--*********************************************
-- COMPUTE SIZE OF CLUSTERED INDEX IF ONE EXISTS
--*********************************************
-- create a temporary table to contain columns in clustered index. System table
-- sysindexkeys has a list of the column numbers contained in the index
select colid into #col_list
from sysindexkeys where id= and indid=1 -- indid=1 means clustered
if (select count(*) from #col_list) >0 -- do the following only if clustered index exsists
begin
-- get total number and total maximum size of fixed-length columns in clustered index
select cols=count(name),
= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select * from #col_list)
if cols= 0 is null. change to 0
select =0
-- get total number and total maximum size of variable-length columns in clustered index
select ey_cols=count(name),
ze= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select * from #col_list)
if ey_cols= 0 is null. change to 0
select ze=0
-- If there are fixed-length columns in the clustered index,
-- a portion of the index row is reserved for the null bitmap. Calculate its size:
if cols <> 0
select map=2+((cols + 7)/8)
else
select map=0
-- If there are variable-length columns in the index, determine how much
-- space is used to store the columns within the index row:
if ey_cols <> 0
select ize=2+(ey_cols *2)+ze
else
select ize=0
-- Calculate the index row size
select =
--Next, calculate the number of index rows per page (8096 free bytes per page):
select _page=(8096)/(+2)
-- consider fillfactor
if =0
select s_per_page = 2
else
select s_per_page= 8096 * ((
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select l_0=ceiling(convert(decimal,(/8192))/(_page-s_per_page))
select s=l_0
select l_x=l_0
while l_x <> 1
begin
select l_y=ceiling(convert(decimal,l_x)/(_page-s_per_page))
select s=s+l_y
select l_x=l_y
end
end
--*********************************************
-- END CLUSTERED INDEX SECTION
--*********************************************
--*********************************************
-- BEGIN NON-CLUSTERED INDEX SECTION
--*********************************************
-- create temp table with non-clustered index info
select indid, colid into #col_list2
from sysindexkeys where id= and indid<>1 -- indid=1 means clustered
if (select count(*) from #col_list2) >0 -- do the following only if non-clustered indexes exsist
begin
declare @i int -- a counter variable
select @i=1 -- initilize to 2, because 1 is id of clustered index
while @i< 249 -- max number of non-clustered indexes
begin
select @i=@i+1 -- look for the next non-clustered index
-- reinitialize all numbers
select ols = 0,
= 0,
y_cols = 0,
e = 0,
ap = 0,
ze = 0,
ze = 0,
er_page = 0,
= 0,
page = 0,
_per_page = 0,
_0 = 0,
_x = 0,
_y = 0,
= 0
-- get total number and total maximum size
-- of fixed-length columns in nonclustered index
select ols=count(name),
= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=0
)
and colid in (select colid from #col_list2 where )
if ols= 0 is null. change to 0
select =0
-- get total number and total maximum size of variable-length columns in index
select y_cols=count(name),
e= sum(length)
from syscolumns
where id= and xtype in
(
select xtype from systypes where variable=1
)
and colid in (select colid from #col_list2 where )
if y_cols= 0 is null. change to 0
select e=0
if ols = 0 and y_cols = 0 --there is no index
continue
-- If there are fixed-length columns in the non-clustered index,
-- a portion of the index row is reserved for the null bitmap. Calculate its size:
if ols <> 0
select ap=2+((ols + 7)/8)
else
select ap=0
-- If there are variable-length columns in the index, determine how much
-- space is used to store the columns within the index row:
if y_cols <> 0
select ze=2+(y_cols *2)+e
else
select ze=0
-- Calculate the non-leaf index row size
select ze=
--Next, calculate the number of non-leaf index rows per page (8096 free bytes per page):
select er_page=(8096)/(ze+2)
-- Next, calculate the leaf index row size
select = + + ze+ap+1
-- Next, calculate the number of leaf level index rows per page
select page = 8096/( + 2)
-- Next, calcuate the number of reserved free index rows/page based on fill factor
if =0
-- a 0 fill factor ALMOST fills up the entire page, but not quite.
--The doc says that fill factor zero leaves 2 empty rows (keys)
--in each index page and no free rows in data pages of clustered
--indexes and leaf pages of non-clustered.
--We are working on the non-clustered index pages in this section
select _per_page=0
else
select _per_page= 8096 * ((
-- Next, calculate the number of pages required to store
-- all the index rows at each level of the index.
select _0=ceiling(convert(decimal,)/page-_per_page)
select =_0
select _x=_0
while _x <> 1
begin
select _y=ceiling(convert(decimal,_x)/er_page)
select =+_y
select _x=_y
end
select ustered_index_pages=ustered_index_pages+
end
end
--*********************************************
-- END NON-CLUSTERED INDEX SECTION
--*********************************************
-- display numbers
select = + s + ustered_index_pages
select ytes=
select bytes= ytes/1024.0
select eg= str(bytes/1000.0,17,2)
select substring(,1,20) as 'Table Name',
convert(varchar(10),eg) as 'MB Estimate',
as 'Total Pages',
as '#Data Pgs',
s as '#Clustered Idx Pgs',
ustered_index_pages as '#NonClustered Idx Pgs'