| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_ijdbc_getprocedurecolumns | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** sp_ijdbc_getprocedurecolumns 4 */ 5 6 7 create procedure sp_ijdbc_getprocedurecolumns( 8 @sp_qualifier varchar(32) = null, /* stored procedure qualifier*/ 9 @sp_owner varchar(32) = null, /* stored procedure owner */ 10 @sp_name varchar(257), /* stored procedure name */ 11 @column_name varchar(257) = null) 12 as 13 declare @msg varchar(250) 14 declare @group_num int 15 declare @semi_position int 16 17 if @@trancount = 0 18 begin 19 set chained off 20 end 21 22 set transaction isolation level 1 23 24 if @sp_qualifier is not null 25 begin 26 if db_name() != @sp_qualifier 27 begin 28 if @sp_qualifier = '' 29 begin 30 select @sp_name = '' 31 select @sp_owner = '' 32 end 33 else 34 begin 35 /* 36 ** 18041, 'Stored Procedure qualifer must be name of 37 ** current database' 38 */ 39 exec sp_getmessage 18041, @msg out 40 raiserror 18041 @msg 41 return (1) 42 end 43 end 44 end 45 else 46 select @sp_qualifier = db_name() 47 48 select @semi_position = charindex(';', @sp_name) 49 if (@semi_position > 0) 50 begin /* If group number separator (;) found */ 51 select @group_num = convert(int, substring(@sp_name, @semi_position + 1, 2)) 52 select @sp_name = substring(@sp_name, 1, @semi_position - 1) 53 end 54 else 55 begin /* No group separator, so default to group number of 1 */ 56 select @group_num = 1 57 end 58 59 if (@sp_owner is null) select @sp_owner = '%' 60 if (@sp_name is null) select @sp_name = '%' 61 if (@column_name is null) select @column_name = '%' 62 63 /* 64 * build a temporary table for holding the results. 65 * The following is from the JDBC specification at 66 * DatabaseMetaData.getProcedureColumns 67 */ 68 69 delete #tmp_getprocedurecolumns 70 71 create table #jproccols_res 72 (PROCEDURE_CAT varchar(32) null, 73 PROCEDURE_SCHEM varchar(32) null, 74 PROCEDURE_NAME varchar(257) not null, 75 COLUMN_NAME varchar(257) not null, 76 COLUMN_TYPE smallint not null, 77 DATA_TYPE smallint not null, 78 TYPE_NAME varchar(32) not null, 79 PRECISION int not null, 80 LENGTH int not null, 81 SCALE smallint not null, 82 RADIX smallint not null, 83 NULLABLE smallint not null, 84 REMARKS varchar(255) null, 85 colid int not null /* hidden, used for ordering */ 86 ) 87 88 /* 89 * insert defined parameters (if any) 90 */ 91 INSERT INTO #jproccols_res 92 SELECT DISTINCT 93 PROCEDURE_CAT = db_name(), 94 PROCEDURE_SCHEM = user_name(o.uid), 95 PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)), 96 COLUMN_NAME = c.name, 97 COLUMN_TYPE = convert(smallint, 0), /*No distinction possible in SQL Server */ 98 DATA_TYPE = jdt.data_type, 99 TYPE_NAME = jdt.type_name, 100 PRECISION = (isnull(convert(int, c.prec), 101 isnull(convert(int, jdt.data_precision), 102 convert(int, c.length))) 103 + isnull(jdt.aux, convert(int, 104 ascii(substring('???AAAFFFCKFOLS', 105 2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))), 106 LENGTH = (isnull(convert(int, c.length), 107 convert(int, jdt.length)) + 108 convert(int, isnull(jdt.aux, 109 ascii(substring('AAA<BB<DDDHJSPP', 110 2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 111 1)) - 64))), 112 SCALE = (isnull(isnull(convert(smallint, c.scale), 113 convert(smallint, jdt.numeric_scale)), 0) + 114 convert(smallint, isnull(jdt.aux, 115 ascii(substring('<<<<<<<<<<<<<<?', 116 2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / c.length, 117 1)) - 60))), 118 RADIX = convert(smallint, 0), 119 NULLABLE = convert(smallint, 2), /* procedureNullableUnknown */ 120 REMARKS = printfmt, 121 colid = c.colid /* parameter position order */ 122 FROM syscolumns c, sysobjects o, sysusers u, sysprocedures p, 123 sybsystemprocs.dbo.spt_jdatatype_info jdt 124 WHERE jdt.ss_dtype = c.type 125 and c.id = o.id and p.id = o.id 126 and user_name(o.uid) like @sp_owner ESCAPE '\' 127 and u.uid = user_id() 128 and o.type = 'P' 129 and o.name like @sp_name ESCAPE '\' 130 and c.name like @column_name ESCAPE '\' 131 and c.number = @group_num 132 and p.number = @group_num 133 134 135 /* 136 * add the 'return parameter' 137 */ 138 INSERT INTO #jproccols_res 139 SELECT DISTINCT 140 PROCEDURE_CAT = db_name(), 141 PROCEDURE_SCHEM = user_name(o.uid), 142 PROCEDURE_NAME = o.name + ';' + ltrim(str(p.number, 5)), 143 COLUMN_NAME = 'RETURN_VALUE', 144 COLUMN_TYPE = convert(smallint, 5), /* procedureColumnReturn */ 145 DATA_TYPE = jdt.data_type, 146 TYPE_NAME = jdt.type_name, 147 PRECISION = (isnull(convert(int, jdt.data_precision), 148 convert(int, jdt.length)) 149 + isnull(jdt.aux, convert(int, 150 ascii(substring('???AAAFFFCKFOLS', 151 2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / jdt.length, 1)) - 60))), 152 LENGTH = (isnull(jdt.length, convert(int, t.length)) + 153 convert(int, isnull(jdt.aux, 154 ascii(substring('AAA<BB<DDDHJSPP', 155 2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / t.length, 156 1)) - 64))), 157 SCALE = (convert(smallint, jdt.numeric_scale) + 158 convert(smallint, isnull(jdt.aux, 159 ascii(substring('<<<<<<<<<<<<<<?', 160 2 * (jdt.ss_dtype % 35 + 1) + 2 - 8 / jdt.length, 161 1)) - 60))), 162 RADIX = convert(smallint, 0), 163 NULLABLE = convert(smallint, 0), /* procedureNoNulls */ 164 REMARKS = 'procedureColumnReturn', 165 colid = 0 /* always the first parameter */ 166 FROM sybsystemprocs.dbo.spt_jdatatype_info jdt, 167 sysobjects o, sysusers u, sysprocedures p, 168 systypes t 169 WHERE jdt.ss_dtype = 56 /* return parameter is an int */ 170 and t.type = jdt.ss_dtype 171 and p.id = o.id 172 and user_name(o.uid) like @sp_owner ESCAPE '\' 173 and u.uid = user_id() 174 and o.type = 'P' 175 and o.name like @sp_name ESCAPE '\' 176 and 'RETURN_VALUE' like @column_name ESCAPE '\' 177 and p.number = @group_num 178 179 180 /* 181 * return the data to the client 182 */ 183 insert #tmp_getprocedurecolumns 184 SELECT PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME, COLUMN_NAME, 185 COLUMN_TYPE, DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE, 186 RADIX, NULLABLE, REMARKS 187 FROM #jproccols_res 188 ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, colid 189 /* 190 * cleanup 191 */ 192
exec sp_procxmode 'sp_ijdbc_getprocedurecolumns', 'AnyMode' go Grant Execute on sp_ijdbc_getprocedurecolumns to public go
| DEFECTS | |
 QJWI 5 Join or Sarg Without Index | 124 |
| QJWI 5 Join or Sarg Without Index | 170 |
 MEST 4 Empty String will be replaced by Single Space | 28 |
| MEST 4 Empty String will be replaced by Single Space | 30 |
| MEST 4 Empty String will be replaced by Single Space | 31 |
| QCAS 4 Cartesian product with single row between tables sybsystemprocs..syscolumns c and [sybsystemprocs..sysusers u], 1 tables with rc=1 | 92 |
| QCAS 4 Cartesian product with single row between tables sybsystemprocs..spt_jdatatype_info jdt and [sybsystemprocs..sysprocedures p, sybsyste... | 139 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {number} | 130 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered (id, number, type, sequence) Intersection: {number} | 132 |
| QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered (id, number, type, sequence) Intersection: {number} | 177 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 131 |
| QTYP 4 Comparison type mismatch smallint = int | 131 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 132 |
| QTYP 4 Comparison type mismatch smallint = int | 132 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int | 169 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 177 |
| QTYP 4 Comparison type mismatch smallint = int | 177 |
| TNOI 4 Table with no index sybsystemprocs..spt_jdatatype_info | sybsystemprocs..spt_jdatatype_info |
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_getprocedurecolumns | |
| MGTP 3 Grant to public sybsystemprocs..spt_jdatatype_info | |
| MGTP 3 Grant to public sybsystemprocs..syscolumns | |
| MGTP 3 Grant to public sybsystemprocs..sysobjects | |
| MGTP 3 Grant to public sybsystemprocs..sysprocedures | |
| MGTP 3 Grant to public sybsystemprocs..systypes | |
| MGTP 3 Grant to public sybsystemprocs..sysusers | |
| MNER 3 No Error Check should check return value of exec | 39 |
| MNER 3 No Error Check should check @@error after delete | 69 |
| MNER 3 No Error Check should check @@error after insert | 91 |
| MNER 3 No Error Check should check @@error after insert | 138 |
| MNER 3 No Error Check should check @@error after insert | 183 |
| MUCO 3 Useless Code Useless Brackets in create proc | 7 |
| MUCO 3 Useless Code Useless Brackets | 41 |
| MUCO 3 Useless Code Useless Brackets | 49 |
| MUCO 3 Useless Code Useless Brackets | 59 |
| MUCO 3 Useless Code Useless Brackets | 60 |
| MUCO 3 Useless Code Useless Brackets | 61 |
| QDIS 3 Check correct use of 'select distinct' | 92 |
| QDIS 3 Check correct use of 'select distinct' | 139 |
| QGWO 3 Group by/Distinct/Union without order by | 92 |
| QGWO 3 Group by/Distinct/Union without order by | 139 |
| QISO 3 Set isolation level | 22 |
| QNAJ 3 Not using ANSI Inner Join | 122 |
| QNAJ 3 Not using ANSI Inner Join | 166 |
| QNUA 3 Should use Alias: Column printfmt should use alias c | 120 |
| QPNC 3 No column in condition | 176 |
| VNRD 3 Variable is not read @sp_qualifier | 46 |
 MTR1 2 Metrics: Comments Ratio Comments: 12% | 7 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 8dec - 2exi + 2 | 7 |
| MTR3 2 Metrics: Query Complexity Complexity: 75 | 7 |
| PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures, sji=sybsystemprocs..spt_jdatatype_info} 0 | 92 |
| PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures, sji=sybsystemprocs..spt_jdatatype_info, t=sybsystemprocs..systypes} 0 | 139 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysobjects reads table sybsystemprocs..spt_jdatatype_info reads table sybsystemprocs..sysprocedures reads table sybsystemprocs..syscolumns calls proc sybsystemprocs..sp_getmessage  calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1)  reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..sysmessages (1) reads table sybsystemprocs..systypes read_writes table tempdb..#jproccols_res (1) writes table tempdb..#tmp_getprocedurecolumns (1) reads table sybsystemprocs..sysusers |