/* Queries - estspace.sql as of 04/15/96  */

/************************************************************************/
PRINT '/*****  SELECT DATABASE: USE master                          *****/'
/************************************************************************/
GO

USE master
GO

/************************************************************************/
PRINT '/*****  IF EXISTS, DROP PROCEDURE                            *****/'
/************************************************************************/
GO

IF EXISTS (SELECT * FROM sysobjects
    WHERE name = 'sp_estspace'
    AND   uid = user_id('dbo')
    AND   type = 'P')
BEGIN
    DROP PROCEDURE dbo.sp_estspace
END
GO

/************************************************************************/
PRINT '/*****    CREATE PROCEDURE   sp_estspace                     *****/'
/************************************************************************/
GO
CREATE PROCEDURE sp_estspace

/*      A procedure to estimate the disk space requirements of a table
**      and its associated indexes.
**      Written by Malcolm Colton 
**      Modifications by Hal Spitz, Jim Panttaja
*/
        (@table_name    varchar(30)=null, /* name of table to estimate */
      @no_of_rows    float = 1,        /* number of rows in the table */
      @fill_factor   float = 0,      /* the fill factor */
      @cols_to_max   varchar(255) =null /* variable length columns for which
                                        to use the maximum rather than 50% of 
                                        the maximum length      */
         )
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 'sp_estspace table_name, no_of_rows, fill_factor, cols_to_max'
        print 'where table_name is the name of the table,'
        print '      no_of_rows is the number of rows in the table,' 
        print '      fill_factor is the index fill factor (default = 0) ' 
        print '      cols_to_max is a list of the variable length columns for which'
        print '                  to use the maximum length instead of the average'
        print '                  (default = null)'
        print 'Examples: sp_estspace titles, 10000, 50, "title, notes"'
        print '          sp_estspace titles, 50000'
        print '          sp_estspace titles, 50000, 0, null, 40'
        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,
     @indlevel_pages    float,
     @key            varchar(30),
     @usertype       tinyint,
     @type           tinyint,
     @indlevel          tinyint,
     @vartype        smallint,
     @more           bit,
     @next_indlevel     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
select  @sum_fixed=0,
     @sum_var=0,
     @sum_avgvar=0,  
     @table_id=0,
     @num_var=0,
     @data_pages=0,
     @row_len=0,
     @sysstat=0

set nocount on

/* Make sure table exists */

select  @sysstat = sysstat,
        @table_id = id
                from sysobjects where name = @table_name
                and uid = user_id()

if @sysstat & 7 not in (1,3)
begin
        select @msg = "The "+@table_name +" table does not exist."
        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

select @fill_factor = @fill_factor / 100.0

/* Create tables for results */

create table #results
        (name   varchar(30),
         type   varchar(12),
         indlevel  tinyint,
         pages  float,
         Kbytes float)
/* Create table of column info for the table to be estimated */ 

select length, type, name, offset
        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 offset !< 0

select @num_var = isnull(count(*),0), @sum_var = isnull(sum(length),0)
        from #col_table
                where offset < 0
                        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 offset < 0
                        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_var +@sum_avgvar) / 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 name, type, indlevel, 
                Pages = str(pages,12,0), Kbytes = str(Kbytes,12,0) 
                from #results

        select Total_Mbytes = str(sum(Kbytes)/1000.0,15,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
                                and indid < 255

        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 @vartype < 0 /* variable: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 @indlevel = 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 indlevel 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_indlevel = @data_pages
           else
                   select @next_indlevel = @no_of_rows

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

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

                   select @temp = @next_indlevel / 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 @indlevel_pages = @temp

                   insert #results values
                           (@index_name, @index_type, @indlevel, @indlevel_pages, 
                                @indlevel_pages * @page_K)

                   if @index_id != 1 and @indlevel = 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_indlevel = @indlevel_pages
                   select @indlevel = @indlevel + 1

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

           /* Account for single root page */
           if @indlevel_pages > 1
                   insert #results values
                           (@index_name, @index_type, @indlevel, 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
                           and indid < 255

end

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

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

drop table #results
drop table #col_table

return
GO

/************************************************************************/
PRINT '/*****  GRANT EXECUTE PERMISSION on sp_estspace to PUBLIC    *****/'
/************************************************************************/
GO

GRANT EXECUTE ON sp_estspace TO public
GO

/************************************************************************/
PRINT '/*****  SCRIPT IS FINISHED EXECUTING                         *****/'
/************************************************************************/
GO