1 /** SECTION END: CLEANUP **/
2
3
4 /* Get a description of a table's optimal set of columns that uniquely
5 ** identifies a row
6 ** Usually it's the unique primary key index column or the identity field
7 */
8
9 create procedure sp_jdbc_getbestrowidentifier(
10 @table_qualifier varchar(32) = null,
11 @table_owner varchar(32) = null,
12 @table_name varchar(255),
13 @scope int,
14 @nullable smallint)
15 as
16 declare @indid int
17 declare @table_id int
18 declare @dbname varchar(32)
19 declare @owner varchar(32)
20 declare @full_table_name varchar(765)
21 declare @msg varchar(765)
22
23 if @@trancount = 0
24 begin
25 set chained off
26 end
27
28 set transaction isolation level 1
29
30 /* this will make sure that all rows are sent even if
31 ** the client "set rowcount" is differect
32 */
33
34 set rowcount 0
35
36
37 if exists (select * from sysobjects where name = '#bestinfo')
38 begin
39 drop table #bestinfo
40 end
41 create table #bestinfo(
42 SCOPE smallint, COLUMN_NAME varchar(255),
43 DATA_TYPE smallint, TYPE_NAME varchar(255),
44 COLUMN_SIZE int, BUFFER_LENGTH varchar(255),
45 DECIMAL_DIGITS smallint, PSEUDO_COLUMN smallint)
46
47 /* get database name */
48 select @dbname = db_name()
49
50 /* we don't want a temp table unless we're in tempdb */
51 /* Adding tempdb check here depending on the ASE version ADDTEMPDB */
52
53 if (@table_name like '#%' and db_name() != db_name(tempdb_id()))
54 begin
55 exec sp_getmessage 17676, @msg output
56 raiserror 17676 @msg
57 return (1)
58 end
59
60 if @table_qualifier is not null
61 begin
62 /* if qualifier doesn't match current database */
63 if @dbname != @table_qualifier
64 begin
65 exec sp_getmessage 18039, @msg output
66 raiserror 18039 @msg
67 return (1)
68 end
69 end
70
71 if (@table_owner is null)
72 begin
73 select @table_owner = '%'
74 end
75 else
76 begin
77
78 if (charindex('%', @table_owner) > 0)
79 begin
80 exec sp_getmessage 17993, @msg output
81 raiserror 17993 @msg, @table_owner
82 return (1)
83 end
84
85 /*
86 ** if there is a '_' character in @table_owner,
87 ** then we need to make it work literally in the like
88 ** clause.
89 */
90 if (charindex('_', @table_owner) > 0)
91 begin
92 exec sp_jdbc_escapeliteralforlike
93 @table_owner output
94 end
95 end
96
97
98 if (@table_name is null)
99 begin
100 exec sp_getmessage 17993, @msg output
101 raiserror 17993 @msg, 'NULL'
102 return (1)
103 end
104
105 if ((select count(*)
106 from sysobjects
107 where user_name(uid) like @table_owner ESCAPE '\'
108 and name = @table_name) = 0)
109 begin
110 exec sp_getmessage 17674, @msg output
111 raiserror 17674 @msg, @table_name
112 return
113 end
114
115 declare owner_cur cursor for
116 select @table_owner = user_name(uid) from sysobjects
117 where name like @table_name ESCAPE '\'
118 and user_name(uid) like @table_owner ESCAPE '\'
119 open owner_cur
120 fetch owner_cur into @owner
121 while (@@sqlstatus = 0)
122 begin
123 select @full_table_name = @owner + '.' + @table_name
124
125 /* get object ID */
126 select @table_id = object_id(@full_table_name)
127
128 /* ROWID, now find the id of the 'best' index for this table */
129
130 select @indid = (
131 select min(indid)
132 from sysindexes
133 where
134 id = @table_id
135 and indid > 0) /* eliminate table row */
136
137 /* Sybase's only PSEUDO_COLUMN is called SYB_IDENTITY_COL and */
138 /* is only generated when dboption 'auto identity' is set on */
139 if exists (select name from syscolumns where id = @table_id and name =
140 'SYB_IDENTITY_COL')
141 begin
142 insert into #bestinfo values (
143 convert(smallint, 0), 'SYB_IDENTITY_COL', 2, 'NUMERIC', 10,
144 'not used', 0, 2)
145 end
146 else
147 begin
148 insert into #bestinfo
149 select
150 convert(smallint, 0), index_col(@full_table_name, indid, c.colid),
151 d.data_type + convert(smallint, isnull(d.aux,
152 ascii(substring('666AAA@@@CB??GG',
153 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
154 - 60)),
155 rtrim(substring(d.type_name, 1 + isnull(d.aux,
156 ascii(substring('III<<<MMMI<<A<A',
157 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
158 - 60), 18)),
159 isnull(d.data_precision, convert(int, c2.length))
160 + isnull(d.aux, convert(int,
161 ascii(substring('???AAAFFFCKFOLS',
162 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
163 - 60)),
164 'not used',
165 /*isnull(d.length, convert(int,c2.length))
166 + convert(int, isnull(d.aux,
167 ascii(substring('AAA168 2*(d.ss_dtype%35+1)+2-8/c2.length, 1))
169 -64)),*/
170 isnull(d.numeric_scale, convert(smallint,
171 isnull(d.aux,
172 ascii(substring('<<<<<<<<<<<<<<?',
173 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
174 - 60))),
175 1
176 from
177 sysindexes x,
178 syscolumns c,
179 sybsystemprocs.dbo.spt_jdbc_datatype_info d,
180 systypes t,
181 syscolumns c2 /* self-join to generate list of index
182 ** columns and to extract datatype names */
183 where
184 x.id = @table_id
185 and c2.name = index_col(@full_table_name, @indid, c.colid)
186 and c2.id = x.id
187 and c.id = x.id
188 and c.colid < keycnt + (x.status & 16) / 16
189 and x.indid = @indid
190 and c2.type = d.ss_dtype
191 and c2.usertype *= t.usertype
192 end
193
194 fetch owner_cur into @owner
195 end /* Adaptive Server has expanded all '*' elements in the following statement */
196 select #bestinfo.SCOPE, #bestinfo.COLUMN_NAME, #bestinfo.DATA_TYPE, #bestinfo.TYPE_NAME, #bestinfo.COLUMN_SIZE, #bestinfo.BUFFER_LENGTH, #bestinfo.DECIMAL_DIGITS, #bestinfo.PSEUDO_COLUMN from #bestinfo
197 drop table #bestinfo
198 return (0)
199
exec sp_procxmode 'sp_jdbc_getbestrowidentifier', 'AnyMode'
go
Grant Execute on sp_jdbc_getbestrowidentifier to public
go
| DEFECTS |
 QJWI 5 Join or Sarg Without Index |
190 |
| QJWI 5 Join or Sarg Without Index |
191 |
 QCSC 4 Costly 'select count()', use 'exists()' |
105 |
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {colid}
Uncovered: [id, number] |
185 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
135 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
188 |
| QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
189 |
| QTYP 4 Comparison type mismatch smallint = int |
189 |
| TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info |
sybsystemprocs..spt_jdbc_datatype_info |
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_getbestrowidentifier |
|
| MGTP 3 Grant to public sybsystemprocs..spt_jdbc_datatype_info |
|
| MGTP 3 Grant to public sybsystemprocs..syscolumns |
|
| MGTP 3 Grant to public sybsystemprocs..sysindexes |
|
| 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 |
55 |
| MNER 3 No Error Check should check return value of exec |
65 |
| MNER 3 No Error Check should check return value of exec |
80 |
| MNER 3 No Error Check should check return value of exec |
92 |
| MNER 3 No Error Check should check return value of exec |
100 |
| MNER 3 No Error Check should check return value of exec |
110 |
| MNER 3 No Error Check should check @@error after insert |
142 |
| MNER 3 No Error Check should check @@error after insert |
148 |
| MUCO 3 Useless Code Useless Brackets in create proc |
9 |
| MUCO 3 Useless Code Useless Brackets |
53 |
| MUCO 3 Useless Code Useless Brackets |
57 |
| MUCO 3 Useless Code Useless Brackets |
67 |
| MUCO 3 Useless Code Useless Brackets |
71 |
| MUCO 3 Useless Code Useless Brackets |
78 |
| MUCO 3 Useless Code Useless Brackets |
82 |
| MUCO 3 Useless Code Useless Brackets |
90 |
| MUCO 3 Useless Code Useless Brackets |
98 |
| MUCO 3 Useless Code Useless Brackets |
102 |
| MUCO 3 Useless Code Useless Brackets |
105 |
| MUCO 3 Useless Code Useless Brackets |
121 |
| MUCO 3 Useless Code Useless Brackets |
198 |
| MUIN 3 Column created using implicit nullability |
41 |
| QAFM 3 Var Assignment from potentially many rows |
116 |
| QISO 3 Set isolation level |
28 |
| QNAO 3 Not using ANSI Outer Join |
176 |
| QNUA 3 Should use Alias: Column indid should use alias x |
150 |
| QNUA 3 Should use Alias: Column keycnt should use alias x |
188 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name} |
37 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id} |
139 |
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] |
186 |
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] |
187 |
| QTJ1 3 Table only appears in inner join clause |
180 |
| VUNU 3 Variable is not used @scope |
13 |
| VUNU 3 Variable is not used @nullable |
14 |
 MRST 2 Result Set Marker |
196 |
| MSUB 2 Subquery Marker |
37 |
| MSUB 2 Subquery Marker |
105 |
| MSUB 2 Subquery Marker |
130 |
| MSUB 2 Subquery Marker |
139 |
| MTR1 2 Metrics: Comments Ratio Comments: 19% |
9 |
| MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 17dec - 6exi + 2 |
9 |
| MTR3 2 Metrics: Query Complexity Complexity: 98 |
9 |
