DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_getbestrowidentifier  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **  sp_ijdbc_getbestrowidentifier
4     */
5     
6     
7     /* Get a description of a table's optimal set of columns that uniquely 
8     ** identifies a row
9     ** Usually it's the unique primary key index column or the identity field
10    */
11    
12    create or replace procedure sp_ijdbc_getbestrowidentifier(
13        @table_qualifier varchar(32) = null,
14        @table_owner varchar(32) = null,
15        @table_name varchar(257),
16        @scope int,
17        @nullable smallint)
18    as
19        declare @indid int
20        declare @table_id int
21        declare @dbname char(30)
22        declare @owner char(30)
23        declare @msg char(70)
24    
25        if @@trancount = 0
26        begin
27            set chained off
28        end
29    
30        set transaction isolation level 1
31    
32    
33        /* get database name */
34        select @dbname = db_name()
35    
36        /* we don't want a temp table unless we're in our temporary db */
37        if @table_name like '#%' and @dbname != db_name(tempdb_id())
38        begin
39            exec sp_getmessage 17676, @msg output
40            raiserror 17676 @msg
41            return (1)
42        end
43    
44        if @table_qualifier is not null
45        begin
46            /* if qualifier doesn't match current database */
47            if @dbname != @table_qualifier
48            begin
49                exec sp_getmessage 18039, @msg output
50                raiserror 18039 @msg
51                return (1)
52            end
53        end
54    
55        if (@table_owner is null)
56        begin
57            select @table_owner = '%'
58        end
59        else
60        begin
61    
62            if (charindex('%', @table_owner) > 0)
63            begin
64                exec sp_getmessage 17993, @msg output
65                raiserror 17993 @msg, @table_owner
66                return (1)
67            end
68    
69            /*
70            ** if there is a '_' character in @table_owner, 
71            ** then we need to make it work literally in the like
72            ** clause.
73            */
74            if (charindex('_', @table_owner) > 0)
75            begin
76                exec sp_ijdbc_escapeliteralforlike
77                    @table_owner output
78            end
79        end
80    
81    
82        if (@table_name is null)
83        begin
84            exec sp_getmessage 17993, @msg output
85            raiserror 17993 @msg, 'NULL'
86            return (1)
87        end
88    
89        delete #tmp_getbestrowidentifier
90    
91        if ((select count(*)
92                    from sysobjects
93                    where user_name(uid) like @table_owner ESCAPE '\'
94                        and name = @table_name) = 0)
95        begin
96            exec sp_getmessage 17674, @msg output
97            raiserror 17674 @msg, @table_name
98            return
99        end
100   
101       select owner = user_name(o.uid),
102           full_table_name = user_name(o.uid) + '.' + @table_name,
103           table_id = object_id(user_name(o.uid) + '.' + @table_name)
104       into #temp_owner
105       from sysobjects o
106       where o.name like @table_name ESCAPE '\'
107           and user_name(o.uid) like @table_owner ESCAPE '\'
108   
109       select min_indid = min(s.indid),
110           table_id = tow.table_id
111       into #temp_ind
112       from sysindexes s, #temp_owner tow
113       where s.indid > 0
114           and s.id = tow.table_id
115       group by tow.table_id
116   
117   
118       /* Sybase's only PSEUDO_COLUMN is called SYB_IDENTITY_COL and */
119       /* is only generated when dboption 'auto identity' is set on */
120   
121   
122       insert #tmp_getbestrowidentifier
123       select convert(smallint, 0), 'SYB_IDENTITY_COL', 2, 'NUMERIC', 10,
124           'not used', 0, 2
125       from #temp_owner
126       where exists (select name from syscolumns
127               where id = #temp_owner.table_id
128                   and name = 'SYB_IDENTITY_COL')
129   
130       insert #tmp_getbestrowidentifier
131       select
132           convert(smallint, 0),
133           index_col(tpo.full_table_name, x.indid, c.colid),
134           d.data_type + convert(smallint, isnull(d.aux,
135               ascii(substring('666AAA@@@CB??GG',
136                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
137               - 60)),
138           rtrim(substring(d.type_name, 1 + isnull(d.aux,
139                       ascii(substring('III<<<MMMI<<A<A',
140                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
141                       - 60), 13)),
142           isnull(d.data_precision, convert(int, c2.length))
143           + isnull(d.aux, convert(int,
144               ascii(substring('???AAAFFFCKFOLS',
145                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
146               - 60)),
147           'not used',
148           /*isnull(d.length, convert(int,c2.length))
149           + convert(int, isnull(d.aux,
150           ascii(substring('AAA151           2*(d.ss_dtype%35+1)+2-8/c2.length, 1))
152           -64)),*/
153           d.numeric_scale + convert(smallint,
154           isnull(d.aux,
155               ascii(substring('<<<<<<<<<<<<<<?',
156                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
157               - 60)),
158           1
159       from
160           sysindexes x,
161           syscolumns c,
162           sybsystemprocs.dbo.spt_jdatatype_info d,
163           systypes t,
164           syscolumns c2, /* self-join to generate list of index
165           ** columns and to extract datatype names */
166           #temp_owner tpo,
167           #temp_ind tind
168       where
169           x.id = tpo.table_id
170           and c2.name = index_col(tpo.full_table_name, tind.min_indid, c.colid)
171           and c2.id = x.id
172           and tind.table_id = tpo.table_id
173           and c.id = x.id
174           and c.colid < keycnt + (x.status & 16) / 16
175           and x.indid = tind.min_indid
176           and c2.type = d.ss_dtype
177           and c2.usertype *= t.usertype
178           and not exists (select name from syscolumns
179               where id = tpo.table_id
180                   and name = 'SYB_IDENTITY_COL')
181   
182       return (0)
183   


exec sp_procxmode 'sp_ijdbc_getbestrowidentifier', 'AnyMode'
go

Grant Execute on sp_ijdbc_getbestrowidentifier to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 170
 QJWI 5 Join or Sarg Without Index 176
 QJWI 5 Join or Sarg Without Index 177
 QCSC 4 Costly 'select count()', use 'exists()' 91
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 113
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 174
 TNOI 4 Table with no index sybsystemprocs..spt_jdatatype_info sybsystemprocs..spt_jdatatype_info
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 114
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 127
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 169
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 171
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 171
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 173
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 173
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 179
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_getbestrowidentifier  
 MGTP 3 Grant to public sybsystemprocs..spt_jdatatype_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  
 MLCH 3 Char type with length>30 char(70) 23
 MNER 3 No Error Check should check return value of exec 39
 MNER 3 No Error Check should check return value of exec 49
 MNER 3 No Error Check should check return value of exec 64
 MNER 3 No Error Check should check return value of exec 76
 MNER 3 No Error Check should check return value of exec 84
 MNER 3 No Error Check should check @@error after delete 89
 MNER 3 No Error Check should check return value of exec 96
 MNER 3 No Error Check should check @@error after select into 101
 MNER 3 No Error Check should check @@error after select into 109
 MNER 3 No Error Check should check @@error after insert 122
 MNER 3 No Error Check should check @@error after insert 130
 MUCO 3 Useless Code Useless Brackets in create proc 12
 MUCO 3 Useless Code Useless Brackets 41
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 86
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 182
 QISO 3 Set isolation level 30
 QJWT 3 Join or Sarg Without Index on temp table 170
 QNAJ 3 Not using ANSI Inner Join 112
 QNAO 3 Not using ANSI Outer Join 159
 QNCO 3 No column in result set 123
 QNUA 3 Should use Alias: Column keycnt should use alias x 174
 QNUA 3 Should use Alias: Table sybsystemprocs..syscolumns 178
 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]
127
 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]
179
 QTJ1 3 Table only appears in inner join clause 163
 VUNU 3 Variable is not used @indid 19
 VUNU 3 Variable is not used @table_id 20
 VUNU 3 Variable is not used @owner 22
 MSUB 2 Subquery Marker 91
 MSUC 2 Correlated Subquery Marker 126
 MSUC 2 Correlated Subquery Marker 178
 MTR1 2 Metrics: Comments Ratio Comments: 17% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 14dec - 6exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 91 12

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#tmp_getbestrowidentifier (1) 
reads table sybsystemprocs..sysindexes  
read_writes table tempdb..#temp_ind (1) 
reads table sybsystemprocs..spt_jdatatype_info  
calls proc sybsystemprocs..sp_ijdbc_escapeliteralforlike  
reads table sybsystemprocs..systypes  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
read_writes table tempdb..#temp_owner (1) 
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..syscolumns