| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_index_row_size_est | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** SP_INDEX_ROW_SIZE_EST 5 ** 6 ** Internal stored sproc to return an estimate of the index row size given 7 ** the table id and the index id. 8 ** 9 ** Parameters: 10 ** @table_id - ID of the object for which index row size is being 11 ** etimated. 12 ** @index_id - ID of the index for which index row size is being 13 ** estimated. 14 ** @table_type - Lock scheme of the object passed. Will be either 15 ** 0(allpages), 1(datapages), or 2(datarows). 16 ** @cols_to_max - comma separated list of var column names for which 17 ** max size is to be used while index row size 18 ** estimation. 19 ** 20 ** Returns: 21 ** @row_size - the estimated index row size 22 ** 23 ** return status = 1 => Invalid table ID 24 ** = 2 => Invalid index ID 25 ** = 3 => Invalid lock scheme 26 ** 27 */ 28 create procedure sp_index_row_size_est( 29 @table_id int 30 , @index_id int 31 , @table_type tinyint 32 , @cols_to_max varchar(2060) = NULL 33 , @row_size int output 34 ) 35 as 36 begin -- { 37 38 /* Declare variables */ 39 declare @sum_fixed int 40 , @sum_var int 41 , @num_var int 42 , @index_name varchar(255) 43 , @index_type varchar(20) 44 , @maxcols_in_key int 45 , @i int 46 , @key varchar(255) 47 , @table_name varchar(511) 48 , @type tinyint 49 , @length int 50 , @vartype smallint 51 , @dol_ind_fixed_ovhd smallint 52 , @dol_ind_var_ovhd smallint 53 , @apl_cind_fixed_ovhd smallint 54 , @apl_cind_var_ovhd smallint 55 , @apl_ncind_fixed_ovhd smallint 56 , @apl_ncind_var_ovhd smallint 57 , @ind_data_len int 58 59 60 /* 61 ** RESOLVE (anushas) Why not return silently without raising errors here 62 ** as this is an internal only sproc? 63 */ 64 65 /* Do parameter check */ 66 if exists (select * from sysobjects where id = @table_id) 67 begin 68 select @index_name = name 69 from sysindexes 70 where id = @table_id 71 and indid = @index_id 72 73 if @index_name is null 74 begin 75 /* 18091, "The target index does not exist." */ 76 raiserror 18091 77 return (2) 78 end 79 end 80 else 81 begin 82 /* 17461, "Object does not exist in this database." */ 83 raiserror 17461 84 return (1) 85 end 86 87 if @table_type not in (0, 1, 2) 88 begin 89 /* 17579, "Lock scheme Unknown or Corrupted" */ 90 raiserror 17579 91 return (3) 92 end 93 94 /* Initialize variables */ 95 select @table_name = object_name(@table_id) 96 , @row_size = 0 97 , @sum_fixed = 0 98 , @sum_var = 0 99 , @num_var = 0 100 , @maxcols_in_key = 31 101 102 103 /* 104 ** Look up each of the key fields for the index, and get the data length 105 ** and the number of variable length keys. 106 */ 107 select @i = 1 108 109 while @i <= @maxcols_in_key 110 begin -- { 111 select @key = index_col(@table_name, @index_id, @i) 112 113 if @key is null 114 break 115 else -- Process one key field 116 begin 117 select @type = type 118 , @length = length 119 , @vartype = offset 120 from syscolumns 121 where id = @table_id 122 and name = @key 123 124 if @vartype < 0 125 select @num_var = @num_var + 1 126 else 127 select @sum_fixed = @sum_fixed + @length 128 129 -- If variable length column, check whether max length 130 -- or average length is to be used. 131 -- 132 if (@type = 37 -- varbinary 133 or @type = 39 -- varchar, nvarchar 134 or @type = 155) -- univarchar 135 begin 136 select @sum_var = @sum_var 137 + (case charindex(@key, 138 @cols_to_max) 139 when 0 then @length / 2 140 else @length 141 end) 142 end 143 end 144 145 select @i = @i + 1 -- Get next key field in this index 146 end -- } -- End of while 147 148 149 150 /* 151 ** Estimate the row size for this index as follows. 152 ** 153 ** Refer to the row formats for clarity (given in the comments at the 154 ** beginning of this file) 155 */ 156 select @dol_ind_fixed_ovhd = 1 + 6 157 , @dol_ind_var_ovhd = 1 + 6 + (2 * @num_var) 158 , @apl_cind_fixed_ovhd = 1 + 4 159 , @apl_cind_var_ovhd = 1 + 4 + 2 + 1 + (1 * @num_var) 160 , @apl_ncind_fixed_ovhd = 1 + 6 161 , @apl_ncind_var_ovhd = 1 + 6 + 2 + 1 + (1 * @num_var) 162 , @ind_data_len = @sum_fixed + @sum_var 163 164 if @table_type = 0 -- APL table 165 begin 166 select @row_size = @ind_data_len 167 + (case @num_var 168 when 0 then (case @index_id 169 when 1 then @apl_cind_fixed_ovhd 170 else @apl_ncind_fixed_ovhd 171 end) 172 else (case @index_id 173 when 1 then @apl_cind_var_ovhd 174 else @apl_ncind_var_ovhd 175 end) 176 end) 177 end 178 else -- DOL table 179 begin 180 select @row_size = @ind_data_len 181 + (case @num_var 182 when 0 then @dol_ind_fixed_ovhd 183 else @dol_ind_var_ovhd 184 end) 185 end 186 end --} -- Procedure done! 187
exec sp_procxmode 'sp_index_row_size_est', 'AnyMode' go Grant Execute on sp_index_row_size_est to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysindexes reads table sybsystemprocs..sysobjects reads table sybsystemprocs..syscolumns CALLERS called by proc sybsystemprocs..sp_spaceusage_object_populate  called by proc sybsystemprocs..sp_spaceusage_object  called by proc sybsystemprocs..sp_spaceusage |
