/* -- DBCC UPDATEUSAGE ('') WITH COUNT_ROWS create table table_space ([Table Name] varchar(100) primary key, [MB Estimate] varchar(20), [Total Pages] int, [#Data Pages] int, [#Clustered Idx Pages] int, [#NonClustered Idx Pages] int) insert table_space exec sp_msforeachtable 'declare @numrows int, @name varchar(50) select @name = replace(replace(substring(''?'',charindex(''.'',''?'') + 1,datalength(''?'')),''['',''''),'']'','''') select @numrows = rows from sysindexes where indid in (0,1) and id = object_id(@name) exec CalcSpace @name,@numrows' select * from table_space --or declare @x int select @x = rows from sysindexes where indid in (0,1) and id = object_id('orders') exec CalcSpace '',@x */ 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 -- 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 (@table_name varchar(100)=null,-- name of table to estimate @num_rows int = 0) -- number of rows in the table as declare @msg varchar(220) -- Give usage statement if @table_name is null if @num_rows = 0 and @table_name is not null goto no_rows if @table_name = null or @num_rows = null begin select 'CalcSpace failed @table =''' + @table_name + ''' @num_rows = ' + cast(@num_Rows as varchar(10)) 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 numeric(18,2), @table_size_in_meg numeric(18,2), @table_size_in_kbytes numeric(18,2), @sysstat smallint, @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 numeric(18,2) -- 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) no_rows: select substring(@table_name,1,100) as 'Table Name', convert(varchar(20),coalesce(@table_size_in_meg,0)) as 'MB Estimate', cast(coalesce(@tot_pages,0) as numeric(18,0)) as 'Total Pages', coalesce(@num_pages,0) as '#Data Pgs', coalesce(@Num_CIndex_Pages,0) as '#Clustered Idx Pgs', coalesce(@total_num_nonclustered_index_pages,0) as '#NonClustered Idx Pgs'