| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_statistics | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 3 4 /* 5 ** Messages for "sp_statistics" 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 ** table cardinality (row count), and number of pages used by 25 ** table (datoampage). 26 */ 27 28 create procedure sp_statistics( 29 @table_name varchar(257), 30 @table_owner varchar(257) = null, 31 @table_qualifier varchar(257) = null, 32 @index_name varchar(257) = '%', 33 @is_unique char(1) = 'N') 34 as 35 declare @indid int 36 declare @lastindid int 37 declare @full_table_name char(520) 38 declare @int_limit int 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 raiserror 18039 51 return (1) 52 end 53 end 54 55 if @@trancount > 0 56 begin 57 /* 58 ** 18040, "Catalog procedure '%1!' can not be run in a transaction. 59 */ 60 raiserror 18040, "sp_statistics" 61 return (1) 62 end 63 else 64 begin 65 set chained off 66 end 67 68 set transaction isolation level 1 69 70 create table #TmpIndex( 71 table_qualifier varchar(257), 72 table_owner varchar(257), 73 table_name varchar(257), 74 index_qualifier varchar(257) null, 75 index_name varchar(257) null, 76 non_unique smallint null, 77 type smallint, 78 seq_in_index smallint null, 79 column_name varchar(257) null, 80 collation char(1) null, 81 index_id int null, 82 cardinality int null, 83 pages int null, 84 status smallint, 85 status2 smallint) 86 87 /* 88 ** Fully qualify table name. 89 */ 90 if @table_owner is null 91 begin /* If unqualified table name */ 92 select @full_table_name = @table_name 93 end 94 else 95 begin /* Qualified table name */ 96 select @full_table_name = @table_owner + '.' + @table_name 97 end 98 99 select @int_limit = 2147483647 100 101 /* 102 ** Start at lowest index id, while loop through indexes. 103 ** Create a row in #TmpIndex for every column in sysindexes, each is 104 ** followed by an row in #TmpIndex with table statistics for the preceding 105 ** index. 106 */ 107 select @indid = min(indid) 108 from sysindexes 109 where id = object_id(@full_table_name) 110 and indid > 0 111 and indid < 255 112 113 while @indid is not NULL 114 begin 115 insert #TmpIndex /* Add all columns that are in index */ 116 select 117 db_name(), /* table_qualifier */ 118 user_name(o.uid), /* table_owner */ 119 o.name, /* table_name */ 120 o.name, /* index_qualifier */ 121 x.name, /* index_name */ 122 0, /* non_unique */ 123 1, /* SQL_INDEX_CLUSTERED */ 124 colid, /* seq_in_index */ 125 INDEX_COL(@full_table_name, x.indid, colid), 126 /* column_name */ 127 index_colorder(@full_table_name, 128 x.indid, colid), /* collation */ 129 @indid, /* index_id */ 130 case /* cardinality */ 131 when row_count(db_id(), x.id) < @int_limit 132 then row_count(db_id(), x.id) 133 else @int_limit 134 end, 135 case /* pages */ 136 when data_pages(db_id(), x.id, 137 case 138 when x.indid = 1 139 then 0 140 else x.indid 141 end) < @int_limit 142 then data_pages(db_id(), x.id, 143 case 144 when x.indid = 1 145 then 0 146 else x.indid 147 end) 148 else @int_limit 149 end, 150 x.status, /* status */ 151 x.status2 /* status2 */ 152 from sysindexes x, syscolumns c, sysobjects o 153 where x.id = object_id(@full_table_name) 154 and x.id = o.id 155 and x.id = c.id 156 and c.colid < keycnt + (x.status & 16) / 16 157 and x.indid = @indid 158 159 /* 160 ** Save last index and increase index id to next higher value. 161 */ 162 select @lastindid = @indid 163 select @indid = NULL 164 165 select @indid = min(indid) 166 from sysindexes 167 where id = object_id(@full_table_name) 168 and indid > @lastindid 169 and indid < 255 170 end 171 172 update #TmpIndex 173 set non_unique = 1 174 where status & 2 != 2 /* If non-unique index */ 175 176 update #TmpIndex 177 set 178 type = 3, /* SQL_INDEX_OTHER */ 179 cardinality = NULL, 180 pages = NULL 181 where index_id > 1 /* If non-clustered index */ 182 183 update #TmpIndex 184 set type = 1 /* SQL_INDEX_CLUSTERED */ 185 where 186 status2 & 512 = 512 /* if placement index */ 187 /* 188 ** Now add row with table statistics 189 */ 190 insert #TmpIndex 191 select 192 db_name(), /* table_qualifier */ 193 user_name(o.uid), /* table_owner */ 194 o.name, /* table_name */ 195 null, /* index_qualifier */ 196 null, /* index_name */ 197 null, /* non_unique */ 198 0, /* SQL_table_STAT */ 199 null, /* seq_in_index */ 200 null, /* column_name */ 201 null, /* collation */ 202 0, /* index_id */ 203 case /* cardinality */ 204 when row_count(db_id(), x.id) < @int_limit 205 then row_count(db_id(), x.id) 206 else @int_limit 207 end, 208 case /* pages */ 209 when data_pages(db_id(), x.id, 210 case 211 when x.indid = 1 212 then 0 213 else x.indid 214 end) < @int_limit 215 then data_pages(db_id(), x.id, 216 case 217 when x.indid = 1 218 then 0 219 else x.indid 220 end) 221 else @int_limit 222 end, 223 0, /* status */ 224 0 /* status2 */ 225 from sysindexes x, sysobjects o 226 where o.id = object_id(@full_table_name) 227 and x.id = o.id 228 and (x.indid = 0 or x.indid = 1) 229 /* 230 ** If there are no indexes 231 ** then table stats are in a row with indid = 0 232 */ 233 234 if @is_unique != 'Y' 235 begin 236 /* If all indexes desired */ 237 select 238 table_qualifier, 239 table_owner, 240 table_name, 241 non_unique, 242 index_qualifier, 243 index_name, 244 type, 245 seq_in_index, 246 column_name, 247 collation, 248 cardinality, 249 pages 250 from #TmpIndex 251 where index_name like @index_name /* If matching name */ 252 or index_name is null /* If SQL_table_STAT row */ 253 order by non_unique, type, index_name, seq_in_index 254 255 end 256 else 257 begin 258 /* else only unique indexes desired */ 259 select 260 table_qualifier, 261 table_owner, 262 table_name, 263 non_unique, 264 index_qualifier, 265 index_name, 266 type, 267 seq_in_index, 268 column_name, 269 collation, 270 cardinality, 271 pages 272 from #TmpIndex 273 where (non_unique = 0 /* If unique */ 274 or non_unique is NULL) /* If SQL_table_STAT row */ 275 and (index_name like @index_name /* If matching name */ 276 or index_name is NULL) /* If SQL_table_STAT row */ 277 order by non_unique, type, index_name, seq_in_index 278 279 end 280 drop table #TmpIndex 281 282 return (0) 283
exec sp_procxmode 'sp_statistics', 'AnyMode' go Grant Execute on sp_statistics to public go
| RESULT SETS | |
| sp_statistics_rset_001 | |
| sp_statistics_rset_002 | |
| DEFECTS | |
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered(id, indid) Intersection: {indid} | 228 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 110 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 111 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 138 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 144 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 156 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 157 |
| QTYP 4 Comparison type mismatch smallint = int | 157 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 168 |
| QTYP 4 Comparison type mismatch smallint = int | 168 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 169 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 211 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 217 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 228 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 273 |
 MGTP 3 Grant to public sybsystemprocs..sp_statistics | |
| MGTP 3 Grant to public sybsystemprocs..syscolumns | |
| MGTP 3 Grant to public sybsystemprocs..sysindexes | |
| MGTP 3 Grant to public sybsystemprocs..sysobjects | |
| MLCH 3 Char type with length>30 char(520) | 37 |
| MNER 3 No Error Check should check @@error after insert | 115 |
| MNER 3 No Error Check should check @@error after update | 172 |
| MNER 3 No Error Check should check @@error after update | 176 |
| MNER 3 No Error Check should check @@error after update | 183 |
| MNER 3 No Error Check should check @@error after insert | 190 |
| MUCO 3 Useless Code Useless Brackets in create proc | 28 |
| MUCO 3 Useless Code Useless Brackets | 51 |
| MUCO 3 Useless Code Useless Brackets | 61 |
| MUCO 3 Useless Code Useless Brackets | 282 |
| MUIN 3 Column created using implicit nullability | 70 |
| QCRS 3 Conditional Result Set | 237 |
| QCRS 3 Conditional Result Set | 259 |
| QISO 3 Set isolation level | 68 |
| QNAJ 3 Not using ANSI Inner Join | 152 |
| QNAJ 3 Not using ANSI Inner Join | 225 |
| QNUA 3 Should use Alias: Column colid should use alias c | 124 |
| QNUA 3 Should use Alias: Column colid should use alias c | 125 |
| QNUA 3 Should use Alias: Column colid should use alias c | 128 |
| QNUA 3 Should use Alias: Column keycnt should use alias x | 156 |
| 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] | 155 |
| QTLO 3 Top-Level OR | 251 |
 MRST 2 Result Set Marker | 237 |
| MRST 2 Result Set Marker | 259 |
| MTR1 2 Metrics: Comments Ratio Comments: 37% | 28 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 10dec - 3exi + 2 | 28 |
| MTR3 2 Metrics: Query Complexity Complexity: 87 | 28 |
| PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 | 116 |
| PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 | 191 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..syscolumns reads table sybsystemprocs..sysobjects read_writes table tempdb..#TmpIndex (1) reads table sybsystemprocs..sysindexes |