try this sizing SP


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

/* Purpose: <purpose of the script> */

/* 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: [list any input parameters] */

/* @table_name VARCHAR(30) Name of table to estimate */

/* @num_rows INT Number of rows in the table */

/* */

/* Output Parameters: [list any output parameters] */

/* - */

/* */

/* Return Status: <list any return codes> */

/* - */

 

/* Usage: <a sample usage statement> */

/* EXEC CalcSpace 'MyTable', 10000 */

/* */

/* Other info: <other info for this SP> */

/* 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: <this section is used to track changes to the script> */

/* Date Author Purpose */

/* 2000-07-04 Magnus Andersson Changed @sysstat from tinyint */

/* to int to prevent overflow */

/* scenario. Added documentation. */

/* */

/************************************************************************/

 

    (@table_name varchar(30)=null,-- name of table to estimate

    @num_rows int = 0) -- number of rows in the table

as

 

declare @msg varchar(120)

 

-- Give usage statement if @table_name is null

 

if @table_name = null or @num_rows = 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 @num_fixed_col int,

    @fixed_data_size int,

    @num_variable_col int,

    @max_var_size int,

    @null_bitmap int,

    @variable_data_size int,

    @table_id int,

    @num_pages int,

    @table_size_in_bytes int,

    @table_size_in_meg real,

    @table_size_in_kbytes real,

    @sysstat int,

    @row_size int,

    @rows_per_page int,

    @free_rows_per_page int,

    @fillfactor int,

    @num_fixed_ckey_cols int,

    @fixed_ckey_size int,

    @num_variable_ckey_cols int,

    @max_var_ckey_size int,

    @cindex_null_bitmap int,

    @variable_ckey_size int,

    @cindex_row_size int,

    @cindex_rows_per_page int,

    @data_space_used int,

    @num_pages_clevel_0 int,

    @num_pages_clevel_1 int,

    @num_pages_clevel_x int,

    @num_pages_clevel_y int,

    @Num_CIndex_Pages int,

    @clustered_index_size_in_bytes int,

    @num_fixed_key_cols int,

    @fixed_key_size int,

    @num_variable_key_cols int,

    @max_var_key_size int,

    @index_null_bitmap int,

    @variable_key_size int,

    @nl_index_row_size int,

    @nl_index_rows_per_page int,

    @index_row_size int,

    @index_rows_per_page int,

    @free_index_rows_per_page int,

    @num_pages_level_0 int,

    @num_pages_level_1 int,

    @num_pages_level_x int,

    @num_pages_level_y int,

    @num_index_pages int,

    @nonclustered_index_size int,

    @total_num_nonclustered_index_pages int,

    @free_cindex_rows_per_page int,

    @tot_pages int

 

-- initialize variables

select @num_fixed_col =0,

    @fixed_data_size =0,

    @num_variable_col =0,

    @max_var_size =0,

    @null_bitmap =0,

    @variable_data_size =0,

    @table_id =0,

    @num_pages =0,

    @table_size_in_bytes =0,

    @table_size_in_meg =0,

    @table_size_in_kbytes =0,

    @sysstat =0,

    @row_size =0,

    @rows_per_page =0,

    @num_fixed_ckey_cols =0,

    @fixed_ckey_size =0,

    @num_variable_ckey_cols =0,

    @max_var_ckey_size =0,

    @cindex_null_bitmap =0,

    @variable_ckey_size =0,

    @cindex_row_size =0,

    @cindex_rows_per_page =0,

    @data_space_used =0,

    @num_pages_clevel_0 =0,

    @num_pages_clevel_1 =0,

    @Num_CIndex_Pages =0,

    @clustered_index_size_in_bytes =0,

    @num_fixed_key_cols =0,

    @fixed_key_size =0,

    @num_variable_key_cols =0,

    @max_var_key_size =0,

    @index_null_bitmap =0,

    @variable_key_size =0,

    @nl_index_row_size =0,

    @nl_index_rows_per_page =0,

    @index_row_size =0,

    @index_rows_per_page =0,

    @free_index_rows_per_page =0,

    @num_pages_level_0 =0,

    @num_pages_level_1 =0,

    @num_pages_level_x =0,

    @num_pages_level_y =0,

    @num_index_pages =0,

    @nonclustered_index_size =0,

    @total_num_nonclustered_index_pages =0,

    @free_cindex_rows_per_page =0,

    @tot_pages =0

 

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

 

--*********************************************

-- ESTIMATE SIZE OF TABLE

--*********************************************

 

-- get total number and total size of fixed-length columns

 

select @num_fixed_col = count(name),

    @fixed_data_size = sum(length)

    from syscolumns

    where id= @table_id and xtype in

    (

    select xtype from systypes where variable=0

    )

 

if @num_fixed_col= 0 --@fixed_data_size is null. change to 0

    select @fixed_data_size=0

 

-- get total number and total maximum size of variable-length columns

 

select @num_variable_col=count(name),

    @max_var_size= sum(length)

    from syscolumns

    where id= @table_id and xtype in

    (

    select xtype from systypes where variable=1

    )

 

if @num_variable_col= 0 --@max_var_size is null. change to 0

    select @max_var_size=0

 

-- get portion of the row used to manage column nullability

 

select @null_bitmap=2+((@num_fixed_col+7)/8)

 

-- determine space needed to store variable-length columns

-- this assumes all variable length columns will be 100% full

if @num_variable_col = 0

    select @variable_data_size=0

else

    select @variable_data_size = 2 + (@num_variable_col *2 )+ @max_var_size

 

-- get row size

 

select @row_size= @fixed_data_size +

    @variable_data_size +

    @null_bitmap + 4 -- 4 represents the data row header

 

 

-- get number of rows per page

 

select @rows_per_page = (8096) / (@row_size+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 @fillfactor = 100 -- initialize it to the maximum

select @free_rows_per_page = 0 --initialize to no free rows/page

select @fillfactor=OrigFillFactor

    from sysindexes

    where id = @table_id and indid=1 -- indid of 1 means the index is clustered

 

if @fillfactor<>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 @free_rows_per_page=8096 * ((100-@fillfactor)/100)/@row_size

 

-- get number of pages needed to store all rows

 

select @num_pages = ceiling(convert(dec,@num_rows) / (@rows_per_page-@free_rows_per_page))

 

-- get storage needed for table data

 

select @data_space_used=8192*@num_pages

 

--*********************************************

-- 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= @table_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 @num_fixed_ckey_cols=count(name),

        @fixed_ckey_size= sum(length)

        from syscolumns

        where id= @table_id and xtype in

        (

        select xtype from systypes where variable=0

        )

        and colid in (select * from #col_list)

 

    if @num_fixed_ckey_cols= 0 --@fixed_ckey_size is null. change to 0

        select @fixed_ckey_size=0

 

    -- get total number and total maximum size of variable-length columns in clustered index

 

    select @num_variable_ckey_cols=count(name),

        @max_var_ckey_size= sum(length)

        from syscolumns

        where id= @table_id and xtype in

        (

        select xtype from systypes where variable=1

        )

        and colid in (select * from #col_list)

 

    if @num_variable_ckey_cols= 0 --@max_var_ckey_size is null. change to 0

        select @max_var_ckey_size=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 @num_fixed_ckey_cols <> 0

        select @cindex_null_bitmap=2+((@num_fixed_ckey_cols + 7)/8)

    else

        select @cindex_null_bitmap=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 @num_variable_ckey_cols <> 0

        select @variable_ckey_size=2+(@num_variable_ckey_cols *2)+@max_var_ckey_size

    else

        select @variable_ckey_size=0

 

    -- Calculate the index row size

 

    select @cindex_row_size=@fixed_ckey_size +@variable_ckey_size+@cindex_null_bitmap+1+8

 

    --Next, calculate the number of index rows per page (8096 free bytes per page):

 

    select @cindex_rows_per_page=(8096)/(@cindex_row_size+2)

 

    -- consider fillfactor

    if @fillfactor=0

        select @free_cindex_rows_per_page = 2

    else

        select @free_cindex_rows_per_page= 8096 * ((100-@fillfactor)/100)/@cindex_row_size

 

    -- Next, calculate the number of pages required to store

    -- all the index rows at each level of the index.

 

    select @num_pages_clevel_0=ceiling(convert(decimal,(@data_space_used/8192))/(@cindex_rows_per_page-@free_cindex_rows_per_page))

    select @Num_CIndex_Pages=@num_pages_clevel_0

    select @num_pages_clevel_x=@num_pages_clevel_0

 

    while @num_pages_clevel_x <> 1

        begin

        select @num_pages_clevel_y=ceiling(convert(decimal,@num_pages_clevel_x)/(@cindex_rows_per_page-@free_cindex_rows_per_page))

        select @Num_CIndex_Pages=@Num_CIndex_Pages+@num_pages_clevel_y

        select @num_pages_clevel_x=@num_pages_clevel_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= @table_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 @num_fixed_key_cols = 0,

        @fixed_key_size = 0,

        @num_variable_key_cols = 0,

        @max_var_key_size = 0,

        @index_null_bitmap = 0,

        @variable_key_size = 0,

        @nl_index_row_size = 0,

        @nl_index_rows_per_page = 0,

        @index_row_size = 0,

        @index_rows_per_page = 0,

        @free_index_rows_per_page = 0,

        @num_pages_level_0 = 0,

        @num_pages_level_x = 0,

        @num_pages_level_y = 0,

        @Num_Index_Pages = 0

 

        -- get total number and total maximum size

        -- of fixed-length columns in nonclustered index

        select @num_fixed_key_cols=count(name),

            @fixed_key_size= sum(length)

            from syscolumns

            where id= @table_id and xtype in

            (

            select xtype from systypes where variable=0

            )

            and colid in (select colid from #col_list2 where indid=@i)

        if @num_fixed_key_cols= 0 --@fixed_key_size is null. change to 0

            select @fixed_key_size=0

 

        -- get total number and total maximum size of variable-length columns in index

 

        select @num_variable_key_cols=count(name),

            @max_var_key_size= sum(length)

            from syscolumns

            where id= @table_id and xtype in

            (

            select xtype from systypes where variable=1

            )

        and colid in (select colid from #col_list2 where indid=@i)

        if @num_variable_key_cols= 0 --@max_var_key_size is null. change to 0

            select @max_var_key_size=0

 

        if @num_fixed_key_cols = 0 and @num_variable_key_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 @num_fixed_key_cols <> 0

            select @index_null_bitmap=2+((@num_fixed_key_cols + 7)/8)

        else

            select @index_null_bitmap=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 @num_variable_key_cols <> 0

            select @variable_key_size=2+(@num_variable_key_cols *2)+@max_var_key_size

        else

            select @variable_key_size=0

 

        -- Calculate the non-leaf index row size

        select @nl_index_row_size=@fixed_key_size +@variable_key_size+@index_null_bitmap+1+8

 

        --Next, calculate the number of non-leaf index rows per page (8096 free bytes per page):

 

        select @nl_index_rows_per_page=(8096)/(@nl_index_row_size+2)

 

        -- Next, calculate the leaf index row size

 

        select @index_row_size=@cindex_row_size + @fixed_key_size + @variable_key_size+@index_null_bitmap+1

 

        -- Next, calculate the number of leaf level index rows per page

 

        select @index_rows_per_page = 8096/(@index_row_size + 2)

 

        -- Next, calcuate the number of reserved free index rows/page based on fill factor

 

        if @fillfactor=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 @free_index_rows_per_page=0

        else

            select @free_index_rows_per_page= 8096 * ((100-@fillfactor)/100)/@index_row_size

 

        -- Next, calculate the number of pages required to store

        -- all the index rows at each level of the index.

 

        select @num_pages_level_0=ceiling(convert(decimal,@num_rows)/@index_rows_per_page-@free_index_rows_per_page)

 

        select @Num_Index_Pages=@num_pages_level_0

        select @num_pages_level_x=@num_pages_level_0

 

        while @num_pages_level_x <> 1

            begin

            select @num_pages_level_y=ceiling(convert(decimal,@num_pages_level_x)/@nl_index_rows_per_page)

            select @Num_Index_Pages=@Num_Index_Pages+@num_pages_level_y

            select @num_pages_level_x=@num_pages_level_y

            end

 

        select @total_num_nonclustered_index_pages=@total_num_nonclustered_index_pages+@Num_Index_Pages

        end

    end

--*********************************************

-- END NON-CLUSTERED INDEX SECTION

--*********************************************

-- display numbers

 

select @tot_pages=@num_pages + @Num_CIndex_Pages + @total_num_nonclustered_index_pages

select @table_size_in_bytes= 8192*@tot_pages

select @table_size_in_kbytes= @table_size_in_bytes/1024.0

select @table_size_in_meg= str(@table_size_in_kbytes/1000.0,17,2)

 

select substring(@table_name,1,20) as 'Table Name',

    convert(varchar(10),@table_size_in_meg) as 'MB Estimate',

    @tot_pages as 'Total Pages',

    @num_pages as '#Data Pgs',

    @Num_CIndex_Pages as '#Clustered Idx Pgs',

    @total_num_nonclustered_index_pages as '#NonClustered Idx Pgs'