create procedure calcspace

/*	A procedure to estimate the disk space requirements of a table
**	and its associated indexes
** Originally Written by Malcolm Colton with assistance from Hal Spitz
** Modified by Sharon Dooley and Jamie Beidleman to make it easier to use


To use the procedure, you must first create the tables in a database. You can use a tiny
database, only the definitions are needed. The indexes should be defined as well. Once
that is done, use this procedure to estimate the amount of space required for each table.


*/
	(@table_name	varchar(30)=null, /* name of table to estimate */
	 @no_of_rows	float = 1)	  /* number of rows in the table */
as

declare @msg	varchar(120)

/*	Give usage statement if @table_name is null */

if @table_name = null or @no_of_rows = 1
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 ' '
	print 'Examples:'
	print ' '
	print 'exec calcspace jobs, 2000'
	print 'exec calcspace publishers, 200'
	print 'exec calcspace employee, 1000'
	print 'exec calcspace titles, 50000'
	return
end

declare	@sum_fixed	int,
	@sum_var	int,
	@sum_avgvar	int,
	@table_id	int,
	@num_var	int,
	@data_pages	float,
	@sysstat	tinyint,
	@temp		float,
	@index_id	int,
	@last_id	int,
	@i		int,
	@level_pages	float,
	@key		varchar(30),
	@usertype	tinyint,
	@type		tinyint,
	@leveln		tinyint,
	@vartype	smallint,
	@more		bit,
	@next_level	float,
	@rows_per_page	smallint,
	@row_len	smallint,
	@length		tinyint,
	@index_name	varchar(30),
	@page_size	smallint,
	@page_K		tinyint,
	@index_type	varchar(20),
	@factor		float,
	@fill_factor	float,
	@cols_to_max	varchar(255),
	@iosec		float

select	@sum_fixed=0,
	@sum_var=0,
	@sum_avgvar=0,	
	@table_id=0,
	@num_var=0,
	@data_pages=0,
	@row_len=0,
	@sysstat=0,
	@fill_factor=0,
	@cols_to_max=null,
	@iosec=30

set nocount on

/* Make sure table exists */

select  @sysstat = sysstat,
	@table_id = id
		from sysobjects where name = @table_name

if @sysstat & 7 not in (1,3)
begin
	select @msg = "I can't find the table "+@table_name
	print @msg
	return
end

/* Get machine page size */

select 	@page_size = low - 32
	from master.dbo.spt_values
		where type = 'E'
		and number = 1

select @page_K = (@page_size +32) /1024

if @fill_factor !=0
	select @fill_factor = @fill_factor / 100.0

/* Create tables for results */

create table #results
	(name	varchar(30),
	 type	varchar(12),
	 leveln	tinyint,
	 pages	float,
	 Kbytes float)

create table #times
	(name 		varchar(30),
	 type	 	varchar(12) null,
	 tot_pages	float,
	 time_mins	float 	null)

/* Create table of column info for the table to be estimated */ 

select length, type, name
	into #col_table
		from syscolumns
			where id = @table_id

/* Look up the important values from this table */

select @sum_fixed = isnull(sum(length),0)
	from #col_table
		where type != 39

select @num_var = isnull(count(*),0), @sum_var = isnull(sum(length),0)
	from #col_table
		where type = 39
			and charindex(name, @cols_to_max) > 0

select @num_var = @num_var + isnull(count(*),0), 
       @sum_avgvar = isnull(sum(length / 2),0)
	from #col_table
		where type = 39
			and charindex(name, @cols_to_max) = 0

/* Calculate the data page requirements */

if @num_var = 0
	select @row_len = 4.0 + @sum_fixed
else
	select @row_len = 8.0 + @sum_fixed + @sum_var +@sum_avgvar + @num_var
				+ (@sum_fixed +@sum_var) / 256.0

/* Allow for fill-factor if set to other than zero */

if @fill_factor = 0	
	select @temp = convert(float, @no_of_rows) * 
		( convert(float, @row_len) / convert(float, @page_size) )
else
begin
	select @temp = convert(float, @no_of_rows) / 
		(convert(float, @page_size) * convert(float, @fill_factor) )
	select @temp = convert(float, @row_len) * @temp
end

/* Now add in allocation pages */
select @temp = @temp +(@temp / 256.0)
select @data_pages = @temp + 1.0
if @data_pages < 8.0
	select @data_pages = 8.0

insert #results values 
	(@table_name, 'data', 0, @data_pages, @data_pages * @page_K)

/* See if the table has any indexes */

select @index_id = min(indid)
	from sysindexes 
		where id = @table_id
			and indid > 0

if @index_id = null	/* We've finished if there are no indexes */
begin
	select @msg = @table_name + ' has no indexes'
	print @msg

	select Total_Mbytes = str(sum(Kbytes)/1000.0,15,0), Kbytes = str(sum(Kbytes),12,0), Pages = str(sum(pages),12,0)
	from #results

	drop table #results
	return
end

select 	@sum_fixed = 0,
	@sum_var = 0,
	@num_var = 0,
	@temp = 0

/* For each index, calculate the important variables
** use them to calculate the index size, and print it */

while @index_id != null
begin
	select @index_name = name 
		from sysindexes
			where id = @table_id
			and indid = @index_id

	if @index_id = 1
		select @index_type = 'clustered'
	else
		select @index_type = 'nonclustered'

	select 	@num_var = 0,
		@sum_var = 0,
		@sum_fixed = 0

	select @i = 1

	/* Look up each of the key fields for the index */

	while @i <= 16
	begin
		select @key = index_col(@table_name, @index_id, @i)

		if @key = null
			break
		else			/* Process one key field */
		begin
			select @type = type, @length = length, @vartype = offset
				from syscolumns
					where id = @table_id
					and name = @key

			if @vartype < 0
				select @num_var = @num_var + 1
			else
				select @sum_fixed = @sum_fixed + @length

			if @type = 39	/* varchar: check if in @cols_to_max */ 
			begin
				if charindex(@key, @cols_to_max) = 0
				   select @sum_var = @sum_var + (@length / 2)
				else
				   select @sum_var = @sum_var + @length
			end
		end

		select @i = @i + 1	/* Get next key field in this index */
	end

	/* Calculate the space used by this index */

	if @num_var = 0
		select @row_len = 5 + @sum_fixed
	else
		select @row_len = @sum_fixed + @sum_var + @num_var + 8

	if @index_id != 1	/* add row id for nc indexes */
		select @row_len = @row_len + 4

	select @leveln = 0

	/* Allow for fill-factor if set to other than zero */

	if @fill_factor = 0	
		select @rows_per_page =  @page_size / @row_len - 2
	else
		select @rows_per_page = @page_size / @row_len * @fill_factor 

	if @rows_per_page > 256
		select @rows_per_page = 256

	/* For clustered indexes, the first leveln of index is based on the 
	** number of data pages. 
	** For nonclustered, it is the number of data rows	*/

	if @index_id = 1
		select @next_level = @data_pages
	else
		select @next_level = @no_of_rows

	select @more = 1	/* Flag for end of index levels */
	while @more = 1
	begin

		/* calculate the number of pages at a single index level */

		select @temp = @next_level / convert(float, @rows_per_page)

		/* Add in a factor for allocation pages */
		if @temp > 200.0
			select @temp = @temp + (@temp /256.0) + 1.0

		select @level_pages = @temp

		insert #results values
			(@index_name, @index_type, @leveln, @level_pages, 
				@level_pages * @page_K)

		if @index_id != 1 and @leveln = 0 /* adjust NC non-leaf rows */ 
			begin
			select @row_len = @row_len - 4 

			/* Allow for fill-factor if set to other than zero */

			if @fill_factor = 0	
				select @rows_per_page = @page_size/@row_len - 2
			else
				select @rows_per_page = @page_size/@row_len*@fill_factor
			end

		if @rows_per_page > 256
			select @rows_per_page = 256

		select @next_level = @level_pages
		select @leveln = @leveln + 1

		/* see if we can fit the next level in 1 page */
		if @rows_per_page >= @next_level 
			select @more = 0
	end

	/* Account for single root page */
	if @level_pages > 1
		insert #results values
			(@index_name, @index_type, @leveln, 1, @page_K)

	/* Now look for next index id for this table */

	select @last_id = @index_id
	select @index_id = null
	select @index_id = min(indid)
		from sysindexes 
			where id = @table_id
			and indid > @last_id

end

/*
select name, type, leveln, Pages = str(pages,12,0), Kbytes = str(Kbytes,12,0) 
from #results
*/

select Total_Mbytes = str(sum(Kbytes)/1000.0,15,0), Kbytes = str(sum(Kbytes),12,0), Pages = str(sum(pages),12,0)
from #results

drop table #results
drop table #col_table
drop table #times

return