DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_getbestrowidentifier  14 déc. 14Defects Propagation Dependencies

1     
2     /* Get a description of a table's optimal set of columns that uniquely 
3     ** identifies a row
4     ** Usually it's the unique primary key index column or the identity field
5     */
6     
7     create or replace procedure sp_jdbc_getbestrowidentifier(
8         @table_qualifier varchar(32) = null,
9         @table_owner varchar(32) = null,
10        @table_name varchar(255),
11        @scope int,
12        @nullable smallint)
13    as
14        declare @indid int
15        declare @table_id int
16        declare @dbname varchar(32)
17        declare @owner varchar(32)
18        declare @full_table_name varchar(765)
19        declare @msg varchar(765)
20    
21        if @@trancount = 0
22        begin
23            set chained off
24        end
25    
26        set transaction isolation level 1
27    
28        /* this will make sure that all rows are sent even if
29        ** the client "set rowcount" is differect
30        */
31    
32        set rowcount 0
33    
34    
35        if exists (select * from sysobjects where name = '#bestinfo')
36        begin
37            drop table #bestinfo
38        end
39        create table #bestinfo(
40            SCOPE smallint, COLUMN_NAME varchar(255),
41            DATA_TYPE smallint, TYPE_NAME varchar(255),
42            COLUMN_SIZE int, BUFFER_LENGTH varchar(255),
43            DECIMAL_DIGITS smallint, PSEUDO_COLUMN smallint)
44    
45        /* get database name */
46        select @dbname = db_name()
47    
48        /* we don't want a temp table unless we're in tempdb */
49        /* Adding tempdb check here depending on the ASE version ADDTEMPDB */
50    
51        if (@table_name like '#%' and db_name() != db_name(tempdb_id()))
52        begin
53            exec sp_getmessage 17676, @msg output
54            raiserror 17676 @msg
55            return (1)
56        end
57    
58        if @table_qualifier is not null
59        begin
60            /* if qualifier doesn't match current database */
61            if @dbname != @table_qualifier
62            begin
63                exec sp_getmessage 18039, @msg output
64                raiserror 18039 @msg
65                return (1)
66            end
67        end
68    
69        if (@table_owner is null)
70        begin
71            select @table_owner = '%'
72        end
73        else
74        begin
75    
76            if (charindex('%', @table_owner) > 0)
77            begin
78                exec sp_getmessage 17993, @msg output
79                raiserror 17993 @msg, @table_owner
80                return (1)
81            end
82    
83            /*
84            ** if there is a '_' character in @table_owner, 
85            ** then we need to make it work literally in the like
86            ** clause.
87            */
88            if (charindex('_', @table_owner) > 0)
89            begin
90                exec sp_jdbc_escapeliteralforlike
91                    @table_owner output
92            end
93        end
94    
95    
96        if (@table_name is null)
97        begin
98            exec sp_getmessage 17993, @msg output
99            raiserror 17993 @msg, 'NULL'
100           return (1)
101       end
102   
103       if ((select count(*)
104                   from sysobjects
105                   where user_name(uid) like @table_owner ESCAPE '\'
106                       and name = @table_name) = 0)
107       begin
108           exec sp_getmessage 17674, @msg output
109           raiserror 17674 @msg, @table_name
110           return
111       end
112   
113       declare owner_cur cursor for
114       select @table_owner = user_name(uid) from sysobjects
115       where name like @table_name ESCAPE '\'
116           and user_name(uid) like @table_owner ESCAPE '\'
117       open owner_cur
118       fetch owner_cur into @owner
119       while (@@sqlstatus = 0)
120       begin
121           select @full_table_name = @owner + '.' + @table_name
122   
123           /* get object ID */
124           select @table_id = object_id(@full_table_name)
125   
126           /* ROWID, now find the id of the 'best' index for this table */
127   
128           select @indid = (
129                   select min(indid)
130                   from sysindexes
131                   where
132                       id = @table_id
133                       and indid > 0) /* eliminate table row */
134   
135           /* Sybase's only PSEUDO_COLUMN is called SYB_IDENTITY_COL and */
136           /* is only generated when dboption 'auto identity' is set on */
137           if exists (select name from syscolumns where id = @table_id and name =
138                       'SYB_IDENTITY_COL')
139           begin
140               insert into #bestinfo values (
141                   convert(smallint, 0), 'SYB_IDENTITY_COL', 2, 'NUMERIC', 10,
142                   'not used', 0, 2)
143           end
144           else
145           begin
146               insert into #bestinfo
147               select
148                   convert(smallint, 0), index_col(@full_table_name, indid, c.colid),
149                   d.data_type + convert(smallint, isnull(d.aux,
150                       ascii(substring('666AAA@@@CB??GG',
151                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
152                       - 60)),
153                   rtrim(substring(d.type_name, 1 + isnull(d.aux,
154                               ascii(substring('III<<<MMMI<<A<A',
155                                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
156                               - 60), 18)),
157                   isnull(d.data_precision, convert(int, c2.length))
158                   + isnull(d.aux, convert(int,
159                       ascii(substring('???AAAFFFCKFOLS',
160                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
161                       - 60)),
162                   'not used',
163                   /*isnull(d.length, convert(int,c2.length))
164                   + convert(int, isnull(d.aux,
165                   ascii(substring('AAA166                   2*(d.ss_dtype%35+1)+2-8/c2.length, 1))
167                   -64)),*/
168                   isnull(d.numeric_scale, convert(smallint,
169                       isnull(d.aux,
170                           ascii(substring('<<<<<<<<<<<<<<?',
171                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
172                           - 60))),
173                   1
174               from
175                   sysindexes x,
176                   syscolumns c,
177                   sybsystemprocs.dbo.spt_jdbc_datatype_info d,
178                   systypes t,
179                   syscolumns c2 /* self-join to generate list of index
180               ** columns and to extract datatype names */
181               where
182                   x.id = @table_id
183                   and c2.name = index_col(@full_table_name, @indid, c.colid)
184                   and c2.id = x.id
185                   and c.id = x.id
186                   and c.colid < keycnt + (x.status & 16) / 16
187                   and x.indid = @indid
188                   and c2.type = d.ss_dtype
189                   and c2.usertype *= t.usertype
190           end
191   
192           fetch owner_cur into @owner
193       end /* Adaptive Server has expanded all '*' elements in the following statement */
194       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
195       drop table #bestinfo
196       return (0)
197   


exec sp_procxmode 'sp_jdbc_getbestrowidentifier', 'AnyMode'
go

Grant Execute on sp_jdbc_getbestrowidentifier to public
go
RESULT SETS
sp_jdbc_getbestrowidentifier_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 188
 QJWI 5 Join or Sarg Without Index 189
 QCSC 4 Costly 'select count()', use 'exists()' 103
 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]
183
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 186
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 187
 QTYP 4 Comparison type mismatch smallint = int 187
 TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info sybsystemprocs..spt_jdbc_datatype_info
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 132
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 137
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 182
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 184
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 184
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 185
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 185
 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 53
 MNER 3 No Error Check should check return value of exec 63
 MNER 3 No Error Check should check return value of exec 78
 MNER 3 No Error Check should check return value of exec 90
 MNER 3 No Error Check should check return value of exec 98
 MNER 3 No Error Check should check return value of exec 108
 MNER 3 No Error Check should check @@error after insert 140
 MNER 3 No Error Check should check @@error after insert 146
 MUCO 3 Useless Code Useless Brackets in create proc 7
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 80
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 196
 MUIN 3 Column created using implicit nullability 39
 QAFM 3 Var Assignment from potentially many rows 114
 QISO 3 Set isolation level 26
 QNAO 3 Not using ANSI Outer Join 174
 QNUA 3 Should use Alias: Column indid should use alias x 148
 QNUA 3 Should use Alias: Column keycnt should use alias x 186
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysobjects.ncsysobjects unique
(name, uid)
Intersection: {name}
35
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
137
 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]
184
 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]
185
 QTJ1 3 Table only appears in inner join clause 178
 MRST 2 Result Set Marker 194
 MSUB 2 Subquery Marker 35
 MSUB 2 Subquery Marker 103
 MSUB 2 Subquery Marker 128
 MSUB 2 Subquery Marker 137
 MTR1 2 Metrics: Comments Ratio Comments: 19% 7
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 18dec - 6exi + 2 7
 MTR3 2 Metrics: Query Complexity Complexity: 98 7

DATA PROPAGATION detailed
ColumnWritten To
@table_namesp_jdbc_getbestrowidentifier_rset_001.COLUMN_NAME

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..systypes  
writes table sybsystemprocs..sp_jdbc_getbestrowidentifier_rset_001 
read_writes table tempdb..#bestinfo (1) 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..spt_jdbc_datatype_info  
calls proc sybsystemprocs..sp_jdbc_escapeliteralforlike  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..sysindexes