DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_primarykey  31 Aug 14Defects Dependencies

1     
2     /*
3     **  sp_ijdbc_primarykey
4     */
5     
6     
7     /*
8     ** Altered from the ODBC sp_pkeys defined in sycsp11.sql.
9     **
10    ** To facilitate eventually combining scripts for ODBC and JDBC,
11    ** only the ordering of the arguments and the final select have been modified.
12    */
13    /*
14    ** note: there is one raiserror message: 18040
15    **
16    ** messages for 'sp_ijdbc_primarykey'               18039, 18040
17    **
18    ** 17461, 'Object does not exist in this database.'
19    ** 18039, 'table qualifier must be name of current database.'
20    ** 18040, 'catalog procedure %1! can not be run in a transaction.', sp_ijdbc_primarykey
21    **
22    */
23    
24    create procedure sp_ijdbc_primarykey
25        @table_qualifier varchar(32),
26        @table_owner varchar(32),
27        @table_name varchar(512)
28    as
29        declare @msg varchar(255)
30        declare @keycnt smallint
31        declare @maxkeycnt smallint
32        declare @indexid smallint
33        declare @indexname varchar(30)
34        declare @i int
35        declare @id int
36        declare @actual_table_name varchar(512)
37    
38        select @actual_table_name = @table_name
39    
40        select @id = NULL
41    
42        set nocount on
43    
44        if (@@trancount = 0)
45        begin
46            set chained off
47        end
48        else
49        begin
50            /* if inside a transaction */
51            /* catalog procedure sp_ijdbc_primarykey can not be run in a transaction.*/
52            exec sp_getmessage 18040, @msg output
53            raiserror 18040 @msg, 'sp_ijdbc_primarykey'
54            return (1)
55        end
56    
57        set transaction isolation level 1
58    
59        if @table_qualifier is not null
60        begin
61            if db_name() != @table_qualifier
62            begin
63                /* if qualifier doesn't match current database */
64                /* 'table qualifier must be name of current database'*/
65                exec sp_getmessage 18039, @msg output
66                raiserror 18039 @msg
67                return (2)
68            end
69        end
70    
71        delete #tmp_primarykey
72    
73    
74        exec sp_ijdbc_escapeliteralforlike @table_name
75    
76        if @table_owner is null
77        begin
78            select @table_owner = '%'
79        end
80    
81        if (select count(*) from sysobjects
82                where user_name(uid) like @table_owner ESCAPE '\'
83                    and name = @table_name) = 0
84        begin
85            /* 17461, 'Object does not exist in this database.' */
86            exec sp_getmessage 17674, @msg output
87            raiserror 17674 @msg
88            return (3)
89        end
90    
91        create table #pkeys(
92            table_qualifier varchar(32),
93            table_owner varchar(32),
94            table_name varchar(257),
95            column_name varchar(257),
96            key_seq smallint,
97            index_name varchar(255))
98    
99    
100   
101       select id = o.id, uid = o.uid, indid = i.indid, name = i.name,
102           keycnt = case when (i.indid >= 2)
103               then i.keycnt - 1
104               else i.keycnt
105           end
106       into #tempkey
107       from sysobjects o, sysindexes i
108       where i.id = o.id
109           and user_name(o.uid) like @table_owner ESCAPE '\'
110           and o.name = @table_name
111           and status2 & 2 = 2 /* make sure it is a declarative constr */
112           and status & 2048 = 2048 /* make sure it is a primary key */
113   
114   
115       select @maxkeycnt = max(keycnt)
116       from #tempkey
117       select @i = 1
118   
119       while @i <= @maxkeycnt
120       begin
121           insert #pkeys select db_name(), user_name(uid), @actual_table_name,
122               index_col(@actual_table_name, indid, @i, uid), @i, name
123           from #tempkey
124           where @i <= keycnt
125           select @i = @i + 1
126       end
127   
128       /*
129       ** Go to the next user/object
130       */
131   
132   
133   
134   
135   
136       /*
137       ** Original ODBC query:
138       **
139       ** select table_qualifier, table_owner, table_name, column_name, key_seq
140       ** from #pkeys
141       ** order by table_qualifier, table_owner, table_name, key_seq
142       */
143       /*
144       ** Primary keys are not explicitly named, so name is always null.
145       */
146   
147       insert #tmp_primarykey
148       select table_qualifier as TABLE_CAT,
149           table_owner as TABLE_SCHEM,
150           table_name as TABLE_NAME,
151           column_name as COLUMN_NAME,
152           key_seq as KEY_SEQ,
153           index_name as PK_NAME
154       from #pkeys
155       order by column_name
156   
157       drop table #pkeys
158       drop table #tempkey
159       return (0)
160   
161   /*
162   **  End of sp_ijdbc_primarykey
163   */
164   


exec sp_procxmode 'sp_ijdbc_primarykey', 'AnyMode'
go

Grant Execute on sp_ijdbc_primarykey to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @pString: varchar(255) = varchar(512) 74
 MTYP 4 Assignment type mismatch table_name: varchar(257) = varchar(512) 121
 MTYP 4 Assignment type mismatch key_seq: smallint = int 122
 QCSC 4 Costly 'select count()', use 'exists()' 81
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 102
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_primarykey  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 52
 MNER 3 No Error Check should check return value of exec 65
 MNER 3 No Error Check should check @@error after delete 71
 MNER 3 No Error Check should check return value of exec 74
 MNER 3 No Error Check should check return value of exec 86
 MNER 3 No Error Check should check @@error after select into 101
 MNER 3 No Error Check should check @@error after insert 121
 MNER 3 No Error Check should check @@error after insert 147
 MUCO 3 Useless Code Useless Brackets 44
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 88
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 159
 MUIN 3 Column created using implicit nullability 91
 QISO 3 Set isolation level 57
 QNAJ 3 Not using ANSI Inner Join 107
 QNUA 3 Should use Alias: Column status2 should use alias i 111
 QNUA 3 Should use Alias: Column status should use alias i 112
 VNRD 3 Variable is not read @id 40
 VUNU 3 Variable is not used @keycnt 30
 VUNU 3 Variable is not used @indexid 32
 VUNU 3 Variable is not used @indexname 33
 MSUB 2 Subquery Marker 81
 MTR1 2 Metrics: Comments Ratio Comments: 30% 24
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 11dec - 4exi + 2 24
 MTR3 2 Metrics: Query Complexity Complexity: 68 24
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 101

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#tempkey (1) 
read_writes table tempdb..#pkeys (1) 
writes table tempdb..#tmp_primarykey (1) 
reads table sybsystemprocs..sysindexes  
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..sysobjects  
calls proc sybsystemprocs..sp_ijdbc_escapeliteralforlike