DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_tables  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_jdbc_tables
3         @table_name varchar(771) = null,
4         @table_owner varchar(32) = null,
5         @table_qualifier varchar(32) = null,
6         @table_type varchar(100) = null
7     as
8         declare @msg varchar(90)
9         declare @searchstr varchar(255)
10    
11        if @@trancount = 0
12        begin
13            set chained off
14        end
15    
16        set transaction isolation level 1
17    
18        /* this will make sure that all rows are sent even if
19        ** the client "set rowcount" is differect
20        */
21    
22        set rowcount 0
23    
24    
25    
26        /* temp table */
27        /* Adding tempdb check here depending on the ASE version ADDTEMPDB */
28    
29        if (@table_name like '#%' and db_name() != db_name(tempdb_id()))
30        begin
31            /*
32            ** Can return data about temp. tables only in tempdb
33            */
34            exec sp_getmessage 17676, @msg out
35            raiserror 17676 @msg
36            return (1)
37        end
38        if @table_qualifier is not null
39        begin
40            if db_name() != @table_qualifier
41            begin
42                exec sp_getmessage 18039, @msg out
43                raiserror 18039 @msg
44                return 1
45            end
46        end
47    
48        if @table_name is null select @table_name = '%'
49        if @table_owner is null select @table_owner = '%'
50    
51        select @searchstr = ''
52        if (patindex('%''SYSTEM%', upper(@table_type)) > 0)
53            select @searchstr = @searchstr + 'S'
54    
55        if (patindex('%''TABLE''%', upper(@table_type)) > 0)
56            select @searchstr = @searchstr + 'U'
57    
58        if (patindex('%''VIEW''%', upper(@table_type)) > 0)
59            select @searchstr = @searchstr + 'V'
60    
61        if @table_type is null
62            select @searchstr = 'SUV'
63        if ((@table_type is not null) and (@searchstr = ''))
64        begin
65            exec sp_getmessage 17301, @msg output
66            raiserror 17301 @msg, @table_type
67            return (3)
68        end
69    
70        /*
71        ** Just return an empty result set with properly named columns
72        ** if (select count(*) from sysobjects where user_name(uid) like @table_owner
73        **    	            and name like @table_name
74        ** 		    and charindex(substring(type,1,1),@searchstr)! = 0) = 0 
75        ** begin
76        ** 	exec sp_getmessage 17674, @msg output
77        ** 	raiserror 17674 @msg
78        ** 	return(1)
79        ** end
80        */
81    
82        select
83            TABLE_CAT = rtrim(db_name()),
84            TABLE_SCHEM = rtrim(user_name(uid)),
85            TABLE_NAME = rtrim(name),
86            rtrim(substring('SYSTEM TABLE            TABLE       VIEW       ',
87                    (ascii(type) - 83) * 12 + 1, 12)) as TABLE_TYPE,
88            REMARKS = convert(varchar(254), null)
89        from sysobjects
90        where name like @table_name ESCAPE '\'
91            and user_name(uid) like @table_owner ESCAPE '\'
92            and charindex(substring(type, 1, 1), @searchstr) != 0
93        order by TABLE_TYPE, TABLE_SCHEM, TABLE_NAME
94    


exec sp_procxmode 'sp_jdbc_tables', 'AnyMode'
go

Grant Execute on sp_jdbc_tables to public
go
RESULT SETS
sp_jdbc_tables_rset_001

DEFECTS
 MEST 4 Empty String will be replaced by Single Space 51
 MEST 4 Empty String will be replaced by Single Space 63
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_tables  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 34
 MNER 3 No Error Check should check return value of exec 42
 MNER 3 No Error Check should check return value of exec 65
 MUCO 3 Useless Code Useless Brackets 29
 MUCO 3 Useless Code Useless Brackets 36
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 67
 QISO 3 Set isolation level 16
 MRST 2 Result Set Marker 82
 MTR1 2 Metrics: Comments Ratio Comments: 23% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 12 = 14dec - 4exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 51 2

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
writes table sybsystemprocs..sp_jdbc_tables_rset_001