if object_id('dbo.sp_EstTableSizeCI', 'P') ISNOTNULLdropprocedure dbo.sp_EstTableSizeCI
go
createprocedure dbo.sp_EstTableSizeCI
@ObjIdint, @Num_Rowsint, @DataPagesint,
@Indidsmallint, @Has_UniqueClusteredIndexint,
@CIndex_Row_Sizeint OUTPUT, @CIPagesint OUTPUT
as/*
**
** Do not call the procedure directly. It will be called
** from the main procedure: sp_estTableSize
**
**
*/set nocount ondeclare@Num_CKey_Colsassmallint, @Num_Variable_CKey_Colsassmallintdeclare@Fixed_CKey_Sizeasint, @Max_Var_CKey_Sizeasintdeclare@CIndex_Null_Bitmapassmallint, @Variable_CKey_Sizeasintdeclare@CIndex_Rows_Per_Pageasintdeclare@Free_CIndex_Rows_Per_Pageasint, @Tasintdeclare@Est_Num_Pagesasintdeclare@Fixed_Sizeasintdeclare@Variable_Colas tinyint, @Var_Sizeasintdeclare@indkeyasint, @TableNameas sysname, @IndexColNameas sysname
set@TableName= object_name(@ObjId)
-- Initialize additive variablesset@Num_CKey_Cols=0set@Fixed_CKey_Size=casewhen@Has_UniqueClusteredIndex=1then0else4endset@Num_Variable_CKey_Cols=0set@Max_Var_CKey_Size=0-- Start with the first column in the clustered indexset@indkey=1
while (@indkey<=16) /* SQL Server 7.0 limits number of columns in an index to 16 */begin-- use Index_Col to get each column of the clustered indexset@IndexColName= INDEX_COL(@TableName, @Indid, @indkey)
if @IndexColNameISNULL Break
set@Num_CKey_Cols=@Num_CKey_Cols+1select@Fixed_Size= sc.length * (1- st.variable),
@Variable_Col= st.variable,
@Var_Size= sc.length * st.variable
from sysobjects as so
join syscolumns as sc
on (so.id = sc.id)
join systypes as st
on (sc.xtype = st.xtype)
where sc.id =@ObjIdand sc.name =@IndexColNameset@Fixed_CKey_Size=@Fixed_CKey_Size+@Fixed_Sizeset@Num_Variable_CKey_Cols=@Num_Variable_CKey_Cols+@Variable_Colset@Max_Var_CKey_Size=@Max_Var_CKey_Size+@Var_Size-- Get the next column in the clustered indexset@indkey=@indkey+1end/* while (@indkey <= 16) */set@CIndex_Null_Bitmap= (2+ (( @Num_CKey_Cols+7) /8 ) )
set@Variable_CKey_Size=casewhen@Num_Variable_CKey_Cols=0then0else2+@Num_Variable_CKey_Cols+@Num_Variable_CKey_Cols+@Max_Var_CKey_Sizeendset@CIndex_Row_Size=@Fixed_CKey_Size+@Variable_CKey_Size+@CIndex_Null_Bitmap+9set@CIndex_Rows_Per_Page=8096/ (@CIndex_Row_Size+2)
-- Get the Fill Factor used in the index (i.e., the Pad_Index factor)declare@Fill_Factorasintselect@Fill_Factor= IndexProperty (@ObjId, object_name(@Indid), 'IndexFillFactor')
from sysindexes
where id =@ObjIdand indid =@Indidset@Free_CIndex_Rows_Per_Page=casewhen@Has_UniqueClusteredIndex=1thencasewhen@Fill_Factor=100then0else2endelsecasewhen@Fill_Factor=100then0else1endend-- Calculate the number of pages required to store all the index rows at each-- level of the indexdeclare@LevelPagesasint, @CLevelasintset@LevelPages=0set@CLevel=@DataPages-- number of pages needed to store table dataset@CIPages=0set@T= (@CIndex_Rows_Per_Page-@Free_CIndex_Rows_Per_Page)
-- According to Books Online (CREATE INDEX (T-SQL)):-- "The number of rows on an intermediate index page is never less than two,-- regardless of how low the value of FILLFACTOR."set@T=casewhen@T<2then2else@Tend
while (@LevelPages<>1)
beginset@LevelPages=ceiling (1.0*@CLevel/@T)
set@CIPages=@CIPages+@LevelPagesset@CLevel=@LevelPagesend