| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_odbc_getindexinfo | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_odbc_getindexinfo" 18039 6 ** 7 ** 18039, "Table qualifier must be name of current database." 8 ** 18040, "Catalog procedure '%1!' can not be run in a transaction. 9 ** 10 */ 11 12 /* 13 ** Sp_statistics returns statistics for the given table, passed as first 14 ** argument. A row is returned for the table and then for each index found 15 ** in sysindexes, starting with lowest value index id in sysindexes and 16 ** proceeding through the highest value index. 17 ** 18 ** Returned rows consist of the columns: 19 ** table qualifier (database name), table owner, table name from sysobjects, 20 ** non_unique (0/1), index qualifier (same as table name), 21 ** index name from sysindexes, type (SQL_INDEX_CLUSTERED/SQL_INDEX_OTHER), 22 ** sequence in index, column name, collation, table cardinality (row count), 23 ** and number of pages used by table (doampg). 24 */ 25 26 create procedure sp_odbc_getindexinfo( 27 @table_name varchar(771), 28 @table_owner varchar(32) = null, 29 @table_qualifier varchar(32) = null, 30 @index_name varchar(771) = '%', 31 @is_unique char(1) = 'N') 32 as 33 declare @indid int 34 declare @lastindid int 35 declare @full_table_name varchar(1543) 36 declare @startedInTransaction bit 37 38 if (@@trancount > 0) 39 select @startedInTransaction = 1 40 else 41 select @startedInTransaction = 0 42 43 /* 44 ** Verify table qualifier is name of current database. 45 */ 46 if @table_qualifier is not null 47 begin 48 if db_name() != @table_qualifier 49 begin /* If qualifier doesn't match current database */ 50 /* 51 ** 18039, "Table qualifier must be name of current database." 52 */ 53 raiserror 18039 54 return (1) 55 end 56 end 57 58 if @@trancount = 0 59 begin 60 set chained off 61 end 62 63 set transaction isolation level 1 64 if (@startedInTransaction = 1) 65 save transaction odbc_keep_temptable_tx 66 67 68 create table #TmpIndex( 69 TABLE_CAT varchar(32), 70 TABLE_SCHEM varchar(32), 71 TABLE_NAME varchar(255), 72 INDEX_QUALIFIER varchar(255) null, 73 INDEX_NAME varchar(255) null, 74 NON_UNIQUE smallint null, 75 TYPE smallint, 76 ORDINAL_POSITION smallint null, 77 COLUMN_NAME varchar(255) null, 78 ASC_OR_DESC char(1) null, 79 index_id int null, 80 CARDINALITY int null, 81 PAGES int null, 82 FILTER_CONDITION varchar(32) null, 83 status smallint, 84 status2 smallint) 85 86 /* 87 ** Fully qualify table name. 88 */ 89 if @table_owner is null 90 begin /* If unqualified table name */ 91 select @full_table_name = @table_name 92 end 93 else 94 begin /* Qualified table name */ 95 select @full_table_name = @table_owner + '.' + @table_name 96 end 97 98 /* 99 ** Start at lowest index id, while loop through indexes. 100 ** Create a row in #TmpIndex for every column in sysindexes, each is 101 ** followed by an row in #TmpIndex with table statistics for the preceding 102 ** index. 103 */ 104 select @indid = min(indid) 105 from sysindexes 106 where id = object_id(@full_table_name) 107 and indid > 0 108 and indid < 255 109 110 while @indid != NULL 111 begin 112 insert #TmpIndex /* Add all columns that are in index */ 113 select 114 db_name(), /* table_qualifier */ 115 user_name(o.uid), /* table_owner */ 116 o.name, /* table_name */ 117 o.name, /* index_qualifier */ 118 x.name, /* index_name */ 119 0, /* non_unique */ 120 1, /* SQL_INDEX_CLUSTERED */ 121 colid, /* seq_in_index */ 122 INDEX_COL(@full_table_name, indid, colid), /* column_name */ 123 index_colorder(@full_table_name, 124 indid, colid), /* collation */ 125 @indid, /* index_id */ 126 -- rowcnt(x.doampg), /* cardinality */ 127 row_count(db_id(), x.id), /* cardinality */ 128 -- data_pgs(x.id,doampg), /* pages */ 129 data_pages(db_id(), x.id, 130 case 131 when x.indid = 1 132 then 0 133 else x.indid 134 end), /* pages */ 135 null, /* Filter condition not available */ 136 /* in SQL Server*/ 137 x.status, /* status */ 138 x.status2 /* status2 */ 139 from sysindexes x, syscolumns c, sysobjects o 140 where x.id = object_id(@full_table_name) 141 and x.id = o.id 142 and x.id = c.id 143 and c.colid < keycnt + (x.status & 16) / 16 144 and x.indid = @indid 145 146 /* 147 ** Save last index and increase index id to next higher value. 148 */ 149 select @lastindid = @indid 150 select @indid = NULL 151 152 select @indid = min(indid) 153 from sysindexes 154 where id = object_id(@full_table_name) 155 and indid > @lastindid 156 and indid < 255 157 end 158 159 update #TmpIndex 160 set NON_UNIQUE = 1 161 where status & 2 != 2 /* If non-unique index */ 162 163 update #TmpIndex 164 set 165 TYPE = 3, /* SQL_INDEX_OTHER */ 166 CARDINALITY = NULL, 167 PAGES = NULL 168 where index_id > 1 /* If non-clustered index */ 169 170 update #TmpIndex 171 set TYPE = 1 /* SQL_INDEX_CLUSTERED */ 172 where 173 status2 & 512 = 512 /* if placement index */ 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 null, /* non_unique */ 185 0, /* SQL_table_STAT */ 186 null, /* seq_in_index */ 187 null, /* column_name */ 188 null, /* collation */ 189 0, /* index_id */ 190 -- rowcnt(x.doampg), /* cardinality */ 191 row_count(db_id(), x.id), /* cardinality */ 192 -- data_pgs(x.id,doampg), /* pages */ 193 data_pages(db_id(), x.id, 194 case 195 when x.indid = 1 196 then 0 197 else x.indid 198 end), /* pages */ 199 null, /* Filter condition not available */ 200 /* in SQL Server*/ 201 0, /* status */ 202 0 /* status2 */ 203 from sysindexes x, sysobjects o 204 where o.id = object_id(@full_table_name) 205 and x.id = o.id 206 and (x.indid = 0 or x.indid = 1) 207 /* 208 ** If there are no indexes 209 ** then table stats are in a row with indid = 0 210 */ 211 212 if @is_unique != 'Y' 213 begin 214 /* If all indexes desired */ 215 select 216 TABLE_CAT, 217 TABLE_SCHEM, 218 TABLE_NAME, 219 NON_UNIQUE, 220 INDEX_QUALIFIER, 221 INDEX_NAME, 222 TYPE, 223 ORDINAL_POSITION, 224 COLUMN_NAME, 225 ASC_OR_DESC, 226 CARDINALITY, 227 PAGES, 228 FILTER_CONDITION 229 from #TmpIndex 230 where INDEX_NAME like @index_name /* If matching name */ 231 or INDEX_NAME is null /* If SQL_table_STAT row */ 232 order by NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION 233 end 234 else 235 begin 236 /* else only unique indexes desired */ 237 select 238 TABLE_CAT, 239 TABLE_SCHEM, 240 TABLE_NAME, 241 NON_UNIQUE, 242 INDEX_QUALIFIER, 243 INDEX_NAME, 244 TYPE, 245 ORDINAL_POSITION, 246 COLUMN_NAME, 247 ASC_OR_DESC, 248 CARDINALITY, 249 PAGES, 250 FILTER_CONDITION 251 from #TmpIndex 252 where (NON_UNIQUE = 0 /* If unique */ 253 or NON_UNIQUE is NULL) /* If SQL_table_STAT row */ 254 and (INDEX_NAME like @index_name /* If matching name */ 255 or INDEX_NAME is NULL) /* If SQL_table_STAT row */ 256 order by NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION 257 258 end 259 260 drop table #TmpIndex 261 if (@startedInTransaction = 1) 262 rollback transaction odbc_keep_temptable_tx 263 264 return (0) 265
exec sp_procxmode 'sp_odbc_getindexinfo', 'AnyMode' go Grant Execute on sp_odbc_getindexinfo to public go
| RESULT SETS | |
| sp_odbc_getindexinfo_rset_002 | |
| sp_odbc_getindexinfo_rset_001 | |
| DEFECTS | |
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered(id, indid) Intersection: {indid} | 206 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 107 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 108 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 131 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 143 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 144 |
| QTYP 4 Comparison type mismatch smallint = int | 144 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 155 |
| QTYP 4 Comparison type mismatch smallint = int | 155 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 156 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 195 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 206 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 252 |
 MGTP 3 Grant to public sybsystemprocs..sp_odbc_getindexinfo | |
| MGTP 3 Grant to public sybsystemprocs..syscolumns | |
| MGTP 3 Grant to public sybsystemprocs..sysindexes | |
| MGTP 3 Grant to public sybsystemprocs..sysobjects | |
| MNAC 3 Not using ANSI 'is null' | 110 |
| MNER 3 No Error Check should check @@error after insert | 112 |
| MNER 3 No Error Check should check @@error after update | 159 |
| MNER 3 No Error Check should check @@error after update | 163 |
| MNER 3 No Error Check should check @@error after update | 170 |
| MNER 3 No Error Check should check @@error after insert | 177 |
| MUCO 3 Useless Code Useless Brackets in create proc | 26 |
| MUCO 3 Useless Code Useless Brackets | 38 |
| MUCO 3 Useless Code Useless Brackets | 54 |
| MUCO 3 Useless Code Useless Brackets | 64 |
| MUCO 3 Useless Code Useless Brackets | 261 |
| MUCO 3 Useless Code Useless Brackets | 264 |
| MUIN 3 Column created using implicit nullability | 68 |
| QCRS 3 Conditional Result Set | 215 |
| QCRS 3 Conditional Result Set | 237 |
| QISO 3 Set isolation level | 63 |
| QNAJ 3 Not using ANSI Inner Join | 139 |
| QNAJ 3 Not using ANSI Inner Join | 203 |
| QNUA 3 Should use Alias: Column colid should use alias c | 121 |
| QNUA 3 Should use Alias: Column colid should use alias c | 122 |
| QNUA 3 Should use Alias: Column indid should use alias x | 122 |
| QNUA 3 Should use Alias: Column colid should use alias c | 124 |
| QNUA 3 Should use Alias: Column indid should use alias x | 124 |
| QNUA 3 Should use Alias: Column keycnt should use alias x | 143 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id} Uncovered: [number, colid] | 142 |
| QTLO 3 Top-Level OR | 230 |
 MRST 2 Result Set Marker | 215 |
| MRST 2 Result Set Marker | 237 |
| MTR1 2 Metrics: Comments Ratio Comments: 40% | 26 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 13dec - 2exi + 2 | 26 |
| MTR3 2 Metrics: Query Complexity Complexity: 88 | 26 |
| PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 | 113 |
| PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 | 178 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..syscolumns reads table sybsystemprocs..sysobjects reads table sybsystemprocs..sysindexes read_writes table tempdb..#TmpIndex (1) |