Database | Proc | Application | Created | Links |
sybsystemprocs | sp_jdbc_getindexinfo ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 4 /* getindexinfo returns information on the indexes of a page 5 ** is unique is set to TRUE only indexes on indexes where it's value's must 6 ** be unique are returned. 7 ** garantee alwys accurate data 8 */ 9 create procedure sp_jdbc_getindexinfo( 10 @table_qualifier varchar(32) = NULL, 11 @table_owner varchar(32) = NULL, 12 @table_name varchar(255), 13 @unique varchar(5), 14 @approximate char(5)) 15 as 16 declare @indid int 17 declare @lastindid int 18 declare @full_table_name varchar(765) 19 declare @msg varchar(250) 20 declare @tableid int 21 declare @startedInTransaction bit 22 23 if (@@trancount = 0) 24 begin 25 set chained off 26 end 27 28 /* see if we are already in a transaction */ 29 if (@@trancount > 0) 30 select @startedInTransaction = 1 31 else 32 select @startedInTransaction = 0 33 34 /* this will make sure that all rows are sent even if 35 ** the client "set rowcount" is differect 36 */ 37 38 set rowcount 0 39 40 /* 41 ** Verify table qualifier is name of current database. 42 */ 43 if @table_qualifier is not null 44 begin 45 if db_name() != @table_qualifier 46 begin /* If qualifier doesn't match current database */ 47 /* 48 ** 18039, 'Table qualifier must be name of current database.' 49 */ 50 exec sp_getmessage 18039, @msg output 51 raiserror 18039 @msg 52 return (1) 53 end 54 end 55 select @table_qualifier = db_name() 56 57 set transaction isolation level 1 58 59 if (@startedInTransaction = 1) 60 save transaction jdbc_keep_temptables_from_tx 61 62 if (@table_owner is null) 63 begin 64 select @table_owner = '%' 65 end 66 67 if (@table_name is null) 68 begin 69 exec sp_getmessage 17993, @msg output 70 raiserror 17993 @msg, 'NULL' 71 return (1) 72 end 73 74 if ((select count(*) 75 from sysobjects 76 where user_name(uid) like @table_owner ESCAPE '\' 77 and name = @table_name) = 0) 78 begin 79 exec sp_getmessage 17674, @msg output 80 raiserror 17674 @msg 81 return 82 end 83 84 create table #TmpIndex( 85 TABLE_CAT varchar(32), 86 TABLE_SCHEM varchar(32), 87 TABLE_NAME varchar(255), 88 INDEX_QUALIFIER varchar(32) null, 89 INDEX_NAME varchar(255) null, 90 NON_UNIQUE varchar(5), 91 TYPE smallint, 92 ORDINAL_POSITION smallint null, 93 COLUMN_NAME varchar(255) null, 94 ASC_OR_DESC char(1) null, 95 index_id int null, 96 CARDINALITY int null, 97 PAGES int null, 98 FILTER_CONDITION varchar(32) null, 99 status smallint, 100 table_id int) 101 102 103 DECLARE jcurs_sysuserobjects CURSOR 104 FOR 105 select id 106 from sysobjects 107 where user_name(uid) like @table_owner ESCAPE '\' 108 and name = @table_name 109 FOR READ ONLY 110 111 OPEN jcurs_sysuserobjects 112 113 FETCH jcurs_sysuserobjects INTO @tableid 114 115 while (@@sqlstatus = 0) 116 begin 117 /* 118 ** build the full_table_name for use below in 119 ** obtaining the index column via the INDEX_COL() 120 ** internal function 121 */ 122 select @full_table_name = user_name(uid) + '.' + name 123 from sysobjects 124 where id = @tableid 125 126 /* 127 ** Start at lowest index id, while loop through indexes. 128 ** Create a row in #TmpIndex for every column in sysindexes, each is 129 ** followed by an row in #TmpIndex with table statistics for the preceding 130 ** index. 131 */ 132 select @indid = min(indid) 133 from sysindexes 134 where id = @tableid 135 and indid > 0 136 and indid < 255 137 138 while @indid is not NULL 139 begin 140 insert #TmpIndex /* Add all columns that are in index */ 141 select 142 db_name(), /* table_qualifier */ 143 user_name(o.uid), /* table_owner */ 144 o.name, /* table_name */ 145 o.name, /* index_qualifier */ 146 x.name, /* index_name */ 147 'FALSE', /* non_unique */ 148 1, /* SQL_INDEX_CLUSTERED */ 149 colid, /* seq_in_index */ 150 INDEX_COL(@full_table_name, indid, colid), /* column_name */ 151 'A', /* collation */ 152 @indid, /* index_id */ 153 /* Server dependent stored procedure add here ADDPOINT_ROWCOUNT*/ 154 row_count(db_id(), x.id), /* cardinality */ 155 data_pages(db_id(), x.id, 156 case 157 when x.indid = 1 158 then 0 159 else x.indid 160 end), /* pages */ 161 162 null, /* Filter condition not available */ 163 /* in SQL Server*/ 164 x.status, /* Status */ 165 @tableid /* table id, internal use for updating the non_unique field */ 166 from sysindexes x, syscolumns c, sysobjects o 167 where x.id = @tableid 168 and x.id = o.id 169 and x.id = c.id 170 and c.colid < keycnt + (x.status & 16) / 16 171 and x.indid = @indid 172 173 /* 174 ** only update the inserts for the current 175 ** owner.table 176 */ 177 update #TmpIndex 178 set NON_UNIQUE = 'TRUE' 179 where status & 2 != 2 /* If non-unique index */ 180 and table_id = @tableid 181 182 /* 183 ** Save last index and increase index id to next higher value. 184 */ 185 select @lastindid = @indid 186 select @indid = NULL 187 188 select @indid = min(indid) 189 from sysindexes 190 where id = @tableid 191 and indid > @lastindid 192 and indid < 255 193 end 194 195 /* 196 ** Now add row with table statistics 197 */ 198 insert #TmpIndex 199 select 200 db_name(), /* table_qualifier */ 201 user_name(o.uid), /* table_owner */ 202 o.name, /* table_name */ 203 null, /* index_qualifier */ 204 null, /* index_name */ 205 'FALSE', /* non_unique */ 206 0, /* SQL_table_STAT */ 207 null, /* seq_in_index */ 208 null, /* column_name */ 209 null, /* collation */ 210 0, /* index_id */ 211 /* Server dependent stored procedure add here ADDPOINT_ROWCOUNT*/ 212 row_count(db_id(), x.id), /* cardinality */ 213 data_pages(db_id(), x.id, 214 case 215 when x.indid = 1 216 then 0 217 else x.indid 218 end), /* pages */ 219 220 null, /* Filter condition not available */ 221 /* in SQL Server*/ 222 0, /* Status */ 223 @tableid /* tableid */ 224 from sysindexes x, sysobjects o 225 where o.id = @tableid 226 and x.id = o.id 227 and (x.indid = 0 or x.indid = 1) 228 /* 229 ** If there are no indexes 230 ** then table stats are in a row with indid = 0 231 */ 232 233 /* 234 ** Go to the next user/object 235 */ 236 FETCH jcurs_sysuserobjects INTO @tableid 237 end 238 239 close jcurs_sysuserobjects 240 deallocate cursor jcurs_sysuserobjects 241 242 update #TmpIndex 243 set 244 TYPE = 3, /* SQL_INDEX_OTHER */ 245 CARDINALITY = NULL, 246 PAGES = NULL 247 where index_id > 1 /* If non-clustered index */ 248 249 if (@unique != '1') 250 begin 251 /* If all indexes desired */ 252 select 253 TABLE_CAT, 254 TABLE_SCHEM, 255 TABLE_NAME, 256 NON_UNIQUE, 257 INDEX_QUALIFIER, 258 INDEX_NAME, 259 TYPE, 260 ORDINAL_POSITION, 261 COLUMN_NAME, 262 ASC_OR_DESC, 263 CARDINALITY, 264 PAGES, 265 FILTER_CONDITION 266 from #TmpIndex 267 order by NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION 268 end 269 else 270 begin 271 /* else only unique indexes desired */ 272 select 273 TABLE_CAT, 274 TABLE_SCHEM, 275 TABLE_NAME, 276 NON_UNIQUE, 277 INDEX_QUALIFIER, 278 INDEX_NAME, 279 TYPE, 280 ORDINAL_POSITION, 281 COLUMN_NAME, 282 ASC_OR_DESC, 283 CARDINALITY, 284 PAGES, 285 FILTER_CONDITION 286 from #TmpIndex 287 where NON_UNIQUE = 'FALSE' 288 order by NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION 289 290 end 291 292 drop table #TmpIndex 293 294 if (@startedInTransaction = 1) 295 rollback transaction jdbc_keep_temptables_from_tx 296 297 return (0) 298 299
exec sp_procxmode 'sp_jdbc_getindexinfo', 'AnyMode' go Grant Execute on sp_jdbc_getindexinfo to public go
RESULT SETS | |
sp_jdbc_getindexinfo_rset_002 | |
sp_jdbc_getindexinfo_rset_001 |
DEFECTS | |
![]() | 145 |
![]() | 74 |
![]() (id, indid) Intersection: {indid} | 227 |
![]() | 135 |
![]() | 136 |
![]() | 157 |
![]() | 170 |
![]() | 171 |
![]() | 171 |
![]() | 191 |
![]() | 191 |
![]() | 192 |
![]() | 215 |
![]() | 227 |
![]() | |
![]() | |
![]() | |
![]() | |
![]() | 50 |
![]() | 69 |
![]() | 79 |
![]() | 140 |
![]() | 177 |
![]() | 198 |
![]() | 242 |
![]() | 9 |
![]() | 23 |
![]() | 29 |
![]() | 52 |
![]() | 59 |
![]() | 62 |
![]() | 67 |
![]() | 71 |
![]() | 74 |
![]() | 115 |
![]() | 249 |
![]() | 294 |
![]() | 297 |
![]() | 84 |
![]() | 252 |
![]() | 272 |
![]() | 57 |
![]() | 166 |
![]() | 224 |
![]() | 149 |
![]() | 150 |
![]() | 150 |
![]() | 170 |
![]() (id, number, colid) Intersection: {id} Uncovered: [number, colid] | 169 |
![]() | 55 |
![]() | 14 |
![]() | 105 |
![]() | 252 |
![]() | 272 |
![]() | 74 |
![]() | 9 |
![]() | 9 |
![]() | 9 |
![]() | 141 |
![]() | 199 |
DEPENDENCIES |
PROCS AND TABLES USED reads table sybsystemprocs..syscolumns ![]() read_writes table tempdb..#TmpIndex (1) calls proc sybsystemprocs..sp_getmessage ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table master..syslanguages (1) ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..sysindexes ![]() |