| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_jdbc_getversioncolumns | | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 create procedure sp_jdbc_getversioncolumns( 4 @table_qualifier varchar(32) = null, 5 @table_owner varchar(32) = null, 6 @table_name varchar(255)) 7 as 8 declare @indid int 9 declare @table_id int 10 declare @dbname varchar(32) 11 declare @full_table_name varchar(765) 12 declare @msg varchar(765) 13 declare @owner varchar(32) 14 15 create table #versionhelp(SCOPE smallint null, COLUMN_NAME varchar(255) null, 16 DATA_TYPE int null, TYPE_NAME varchar(8) null, COLUMN_SIZE int null, 17 BUFFER_LENGTH smallint null, DECIMAL_DIGITS smallint null, 18 PSEUDO_COLUMN smallint null) 19 20 if @@trancount = 0 21 begin 22 set chained off 23 end 24 25 set transaction isolation level 1 26 27 /* this will make sure that all rows are sent even if 28 ** the client "set rowcount" is differect 29 */ 30 31 set rowcount 0 32 33 34 /* get database name */ 35 select @dbname = db_name() 36 37 /* we don't want a temp table unless we're in tempdb */ 38 /* Adding tempdb check here depending on the ASE version ADDTEMPDB */ 39 40 if (@table_name like '#%' and db_name() != db_name(tempdb_id())) 41 begin 42 exec sp_getmessage 17676, @msg output 43 raiserror 17676 @msg 44 return (1) 45 end 46 47 if @table_qualifier is not null 48 begin 49 /* if qualifier doesn't match current database */ 50 if @dbname != @table_qualifier 51 begin 52 exec sp_getmessage 18039, @msg output 53 raiserror 18039 @msg 54 return (1) 55 end 56 end 57 58 if (@table_owner is null) select @table_owner = '%' 59 else 60 begin 61 /* 62 ** NOTE: SQL Server allows an underscore '_' in the table owner, even 63 ** though it is a single character wildcard. 64 */ 65 if (charindex('%', @table_owner) > 0) 66 begin 67 exec sp_getmessage 17993, @msg output 68 raiserror 17993 @msg, @table_owner 69 return (1) 70 end 71 exec sp_jdbc_escapeliteralforlike @table_owner output 72 end 73 74 if (@table_name is null) 75 begin 76 exec sp_getmessage 17993, @msg output 77 raiserror 17993 @msg, 'NULL' 78 return (1) 79 end 80 81 if (select count(*) 82 from sysobjects 83 where user_name(uid) 84 like @table_owner ESCAPE '\' 85 and name = @table_name) = 0 86 begin 87 exec sp_getmessage 17674, @msg output 88 raiserror 17674 @msg 89 return 1 90 end 91 else 92 begin 93 declare version_cur cursor for 94 select @table_owner = user_name(uid) from sysobjects 95 where name = @table_name and user_name(uid) like @table_owner 96 97 open version_cur 98 fetch version_cur into @owner 99 100 while (@@sqlstatus = 0) 101 begin 102 if @owner is null 103 begin /* if unqualified table name */ 104 select @full_table_name = @table_name 105 end 106 else 107 begin /* qualified table name */ 108 select @full_table_name = @owner + '.' + @table_name 109 end 110 111 /* get object ID */ 112 select @table_id = object_id(@full_table_name) 113 114 insert into #versionhelp select 115 convert(smallint, 0), 116 c.name, 117 (select data_type from 118 sybsystemprocs.dbo.spt_jdbc_datatype_info 119 where type_name = 'binary'), 120 'BINARY', 121 isnull(d.data_precision, 122 convert(int, c.length)) 123 + isnull(d.aux, convert(int, 124 ascii(substring('???AAAFFFCKFOLS', 125 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 126 - 60)), 127 18, /* Number of chars = 2^4 byte + '0x' */ 128 isnull(d.numeric_scale + convert(smallint, 129 isnull(d.aux, 130 ascii(substring('<<<<<<<<<<<<<<?', 131 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 132 - 60)), 0), 133 1 134 from 135 systypes t, syscolumns c, 136 sybsystemprocs.dbo.spt_jdbc_datatype_info d 137 where 138 c.id = @table_id 139 and c.type = d.ss_dtype 140 and c.usertype = 80 /* TIMESTAMP */ 141 and t.usertype = 80 /* TIMESTAMP */ 142 fetch version_cur into @owner 143 end 144 end /* Adaptive Server has expanded all '*' elements in the following statement */ 145 select #versionhelp.SCOPE, #versionhelp.COLUMN_NAME, #versionhelp.DATA_TYPE, #versionhelp.TYPE_NAME, #versionhelp.COLUMN_SIZE, #versionhelp.BUFFER_LENGTH, #versionhelp.DECIMAL_DIGITS, #versionhelp.PSEUDO_COLUMN from #versionhelp 146
exec sp_procxmode 'sp_jdbc_getversioncolumns', 'AnyMode' go Grant Execute on sp_jdbc_getversioncolumns to public go
| RESULT SETS | |
| sp_jdbc_getversioncolumns_rset_001 | |
| DEFECTS | |
 QJWI 5 Join or Sarg Without Index | 139 |
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..systypes t and [sybsystemprocs..syscolumns c, sybsystemprocs..spt_jdb... | 114 |
| QCSC 4 Costly 'select count()', use 'exists()' | 81 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 140 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int | 141 |
| TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info | sybsystemprocs..spt_jdbc_datatype_info |
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_getversioncolumns | |
| MGTP 3 Grant to public sybsystemprocs..spt_jdbc_datatype_info | |
| MGTP 3 Grant to public sybsystemprocs..syscolumns | |
| MGTP 3 Grant to public sybsystemprocs..sysobjects | |
| MGTP 3 Grant to public sybsystemprocs..systypes | |
| MNER 3 No Error Check should check return value of exec | 42 |
| MNER 3 No Error Check should check return value of exec | 52 |
| MNER 3 No Error Check should check return value of exec | 67 |
| MNER 3 No Error Check should check return value of exec | 71 |
| MNER 3 No Error Check should check return value of exec | 76 |
| MNER 3 No Error Check should check return value of exec | 87 |
| MNER 3 No Error Check should check @@error after insert | 114 |
| MUCO 3 Useless Code Useless Brackets in create proc | 3 |
| MUCO 3 Useless Code Useless Brackets | 40 |
| MUCO 3 Useless Code Useless Brackets | 44 |
| MUCO 3 Useless Code Useless Brackets | 54 |
| MUCO 3 Useless Code Useless Brackets | 58 |
| MUCO 3 Useless Code Useless Brackets | 65 |
| MUCO 3 Useless Code Useless Brackets | 69 |
| MUCO 3 Useless Code Useless Brackets | 74 |
| MUCO 3 Useless Code Useless Brackets | 78 |
| MUCO 3 Useless Code Useless Brackets | 100 |
| QISO 3 Set isolation level | 25 |
| QNAJ 3 Not using ANSI Inner Join | 134 |
| QNUA 3 Should use Alias: Table sybsystemprocs..spt_jdbc_datatype_info | 118 |
| QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id} | 138 |
| VUNU 3 Variable is not used @indid | 8 |
 MRST 2 Result Set Marker | 145 |
| MSUB 2 Subquery Marker | 81 |
| MSUB 2 Subquery Marker | 117 |
| MTR1 2 Metrics: Comments Ratio Comments: 16% | 3 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 13dec - 6exi + 2 | 3 |
| MTR3 2 Metrics: Query Complexity Complexity: 77 | 3 |
| PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, sjdi=sybsystemprocs..spt_jdbc_datatype_info} 0 | 114 |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysobjects calls proc sybsystemprocs..sp_jdbc_escapeliteralforlike reads table sybsystemprocs..spt_jdbc_datatype_info read_writes table tempdb..#versionhelp (1) 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 |