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