DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_getschemas_cts  31 Aug 14Defects Dependencies

1     /** SECTION END: CLEANUP **/
2     
3     
4     CREATE PROCEDURE sp_jdbc_getschemas_cts
5     as
6         declare @schemaname varchar(32)
7         declare @startedInTransaction bit
8     
9         if @@trancount = 0
10        begin
11            set chained off
12        end
13        /* check if we're in a transaction, before we try any select statements */
14        if (@@trancount > 0)
15            select @startedInTransaction = 1
16        else
17            select @startedInTransaction = 0
18    
19        set transaction isolation level 1
20    
21        if (@startedInTransaction = 1)
22            save transaction jdbc_keep_temptables_from_tx
23    
24        /* this will make sure that all rows are sent even if
25        ** the client "set rowcount" is differect
26        */
27    
28        set rowcount 0
29    
30        create table #tmpschemas
31        (TABLE_SCHEM varchar(32) null)
32    
33        DECLARE jcurs_getschemas CURSOR
34        FOR
35        select name from sysusers where suid >= - 2
36        FOR READ ONLY
37        OPEN jcurs_getschemas
38        FETCH jcurs_getschemas INTO @schemaname
39        while (@@sqlstatus = 0)
40        begin
41            insert into #tmpschemas values (@schemaname)
42            FETCH jcurs_getschemas INTO @schemaname
43        end
44    
45        close jcurs_getschemas
46        deallocate cursor jcurs_getschemas
47    
48        select TABLE_SCHEM from #tmpschemas order by TABLE_SCHEM
49        drop table #tmpschemas
50        if (@startedInTransaction = 1)
51            rollback transaction jdbc_keep_temptables_from_tx
52    


exec sp_procxmode 'sp_jdbc_getschemas_cts', 'AnyMode'
go

Grant Execute on sp_jdbc_getschemas_cts to public
go
RESULT SETS
sp_jdbc_getschemas_cts_rset_001

DEFECTS
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_getschemas_cts  
 MGTP 3 Grant to public sybsystemprocs..sysusers  
 MNER 3 No Error Check should check @@error after insert 41
 MUCO 3 Useless Code Useless Brackets 14
 MUCO 3 Useless Code Useless Brackets 21
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 50
 QISO 3 Set isolation level 19
 CRDO 2 Read Only Cursor Marker (has for read only clause) 35
 MRST 2 Result Set Marker 48
 MTR1 2 Metrics: Comments Ratio Comments: 13% 4
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 6 = 5dec - 1exi + 2 4
 MTR3 2 Metrics: Query Complexity Complexity: 35 4

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysusers  
read_writes table tempdb..#tmpschemas (1)