DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_tables  31 Aug 14Defects Dependencies

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


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 52
 MEST 4 Empty String will be replaced by Single Space 64
 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 35
 MNER 3 No Error Check should check return value of exec 43
 MNER 3 No Error Check should check return value of exec 66
 MUCO 3 Useless Code Useless Brackets 30
 MUCO 3 Useless Code Useless Brackets 37
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 68
 QISO 3 Set isolation level 17
 MRST 2 Result Set Marker 83
 MTR1 2 Metrics: Comments Ratio Comments: 24% 3
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 13dec - 4exi + 2 3
 MTR3 2 Metrics: Query Complexity Complexity: 51 3

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