DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_tables  31 Aug 14Defects Dependencies

1     
2     create procedure sp_ijdbc_tables
3         @table_name varchar(257) = 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        /* temp table */
19        if (@table_name like '#%' and
20                db_name() != db_name(tempdb_id()))
21        begin
22            /*
23            ** Can return data about temp. tables only from your temporary db
24            */
25            exec sp_getmessage 17676, @msg out
26            raiserror 17676 @msg
27            return (1)
28        end
29        if @table_qualifier is not null
30        begin
31            if db_name() != @table_qualifier
32            begin
33                exec sp_getmessage 18039, @msg out
34                raiserror 18039 @msg
35                return 1
36            end
37        end
38    
39        if @table_name is null select @table_name = '%'
40        if @table_owner is null select @table_owner = '%'
41    
42        select @searchstr = ''
43        if (patindex('%''SYSTEM%', upper(@table_type)) > 0)
44            select @searchstr = @searchstr + 'S'
45    
46        if (patindex('%''TABLE''%', upper(@table_type)) > 0)
47            select @searchstr = @searchstr + 'U'
48    
49        if (patindex('%''VIEW''%', upper(@table_type)) > 0)
50            select @searchstr = @searchstr + 'V'
51    
52        if @table_type is null
53            select @searchstr = 'SUV'
54        if ((@table_type is not null) and (@searchstr = ''))
55        begin
56            exec sp_getmessage 17301, @msg output
57            raiserror 17301 @msg, @table_type
58            return (3)
59        end
60    
61        delete #tmp_tables
62    
63        /*
64        ** Just return an empty result set with properly named columns
65        ** if (select count(*) from sysobjects where user_name(uid) like @table_owner
66        **    	            and name like @table_name
67        ** 		    and charindex(substring(type,1,1),@searchstr)! = 0) = 0 
68        ** begin
69        ** 	exec sp_getmessage 17674, @msg output
70        ** 	raiserror 17674 @msg
71        ** 	return(1)
72        ** end
73        */
74    
75        insert #tmp_tables
76        select
77            TABLE_CAT = rtrim(db_name()),
78            TABLE_SCHEM = rtrim(user_name(uid)),
79            TABLE_NAME = rtrim(name),
80            rtrim(substring('SYSTEM TABLE            TABLE       VIEW       ',
81                    (ascii(type) - 83) * 12 + 1, 12)) as TABLE_TYPE,
82            REMARKS = convert(varchar(254), null)
83        from sysobjects
84        where name like @table_name ESCAPE '\'
85            and user_name(uid) like @table_owner ESCAPE '\'
86            and charindex(substring(type, 1, 1), @searchstr) != 0
87        order by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME
88    


exec sp_procxmode 'sp_ijdbc_tables', 'AnyMode'
go

Grant Execute on sp_ijdbc_tables to public
go
DEFECTS
 MEST 4 Empty String will be replaced by Single Space 42
 MEST 4 Empty String will be replaced by Single Space 54
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_tables  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 25
 MNER 3 No Error Check should check return value of exec 33
 MNER 3 No Error Check should check return value of exec 56
 MNER 3 No Error Check should check @@error after delete 61
 MNER 3 No Error Check should check @@error after insert 75
 MUCO 3 Useless Code Useless Brackets 19
 MUCO 3 Useless Code Useless Brackets 27
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 49
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 58
 QISO 3 Set isolation level 16
 MTR1 2 Metrics: Comments Ratio Comments: 17% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 13dec - 4exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 53 2

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