DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_datatype_info  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     **  sp_ijdbc_datatype_info
4     */
5     
6     create or replace procedure sp_ijdbc_datatype_info
7     as
8     
9         if @@trancount = 0
10        begin
11            set chained off
12        end
13    
14        set transaction isolation level 1
15    
16        delete #tmp_datatype_info
17    
18        insert #tmp_datatype_info
19        select /* Real SQL Server data types */
20            t.name as TYPE_NAME,
21            d.data_type as DATA_TYPE,
22            isnull(d.data_precision, convert(int, t.length)) as 'PRECISION',
23            d.literal_prefix as LITERAL_PREFIX,
24            d.literal_suffix as LITERAL_SUFFIX,
25            e.create_params as CREATE_PARAMS,
26            d.nullable as NULLABLE,
27            d.case_sensitive as CASE_SENSITIVE,
28            d.searchable as SEARCHABLE,
29            d.unsigned_attribute as UNSIGNED_ATTRIBUTE,
30            d.money as FIXED_PREC_SCALE,
31            d.auto_increment as AUTO_INCREMENT,
32            d.local_type_name as LOCAL_TYPE_NAME,
33            d.minimum_scale as MINIMUM_SCALE,
34            d.maximum_scale as MAXIMUM_SCALE,
35            d.sql_data_type as SQL_DATA_TYPE,
36            d.sql_datetime_sub as SQL_DATETIME_SUB,
37            d.num_prec_radix as NUM_PREC_RADIX,
38            d.interval_precision as INTERVAL_PRECISION
39        from sybsystemprocs.dbo.spt_jdatatype_info d,
40            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
41        where d.ss_dtype = t.type and t.usertype *= e.user_type
42            /* restrict results to 'real' datatypes, exclude float*/
43            and t.name not in ('nchar', 'nvarchar', 'sysname', 'longsysname',
44                'timestamp', 'float', 'datetimn', 'floatn', 'intn', 'moneyn')
45            and t.usertype < 100 /* No user defined types */
46        UNION
47        select /* SQL Server user data types */
48            t.name, d.data_type,
49            isnull(d.data_precision, convert(int, t.length)) as 'PRECISION',
50            d.literal_prefix, d.literal_suffix, e.create_params, d.nullable,
51            d.case_sensitive, d.searchable, d.unsigned_attribute, d.money,
52            d.auto_increment, t.name, d.minimum_scale,
53            d.maximum_scale, d.sql_data_type, d.sql_datetime_sub,
54            d.num_prec_radix, d.interval_precision
55        from sybsystemprocs.dbo.spt_jdatatype_info d,
56            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
57        where d.ss_dtype = t.type and t.usertype *= e.user_type
58            /* Restrict to user defined types (value > 100)  and Sybase user defined 
59            ** types (listed)*/
60            and (t.name in ('nchar', 'nvarchar', 'sysname', 'longsysname')
61                or t.usertype >= 100) /* User defined types */
62        UNION
63        select /* ADD double precision which is floatn internally*/
64            'double precision', 8, 15,
65            d.literal_prefix, d.literal_suffix, e.create_params, d.nullable,
66            d.case_sensitive, d.searchable, d.unsigned_attribute, d.money,
67            d.auto_increment, 'double precision', d.minimum_scale,
68            d.maximum_scale, d.sql_data_type, d.sql_datetime_sub,
69            d.num_prec_radix, d.interval_precision
70        from sybsystemprocs.dbo.spt_jdatatype_info d,
71            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
72        where d.ss_dtype = t.type and t.usertype *= e.user_type
73            and t.name = 'floatn' and t.usertype < 100
74        UNION /* ADD float now */
75        select
76            'float', 8, 8,
77            d.literal_prefix, d.literal_suffix, e.create_params, d.nullable,
78            d.case_sensitive, d.searchable, d.unsigned_attribute, d.money,
79            d.auto_increment, 'float', d.minimum_scale,
80            d.maximum_scale, d.sql_data_type, d.sql_datetime_sub,
81            d.num_prec_radix, d.interval_precision
82        from sybsystemprocs.dbo.spt_jdatatype_info d,
83            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
84        where d.ss_dtype = t.type and t.usertype *= e.user_type
85            and t.name = 'float' and t.usertype < 100
86        order by d.data_type, t.name
87    
88        return (0)
89    


exec sp_procxmode 'sp_ijdbc_datatype_info', 'AnyMode'
go

Grant Execute on sp_ijdbc_datatype_info to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 41
 QJWI 5 Join or Sarg Without Index 57
 QJWI 5 Join or Sarg Without Index 72
 QJWI 5 Join or Sarg Without Index 84
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 45
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 61
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 73
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 85
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info_ext sybsystemprocs..spt_datatype_info_ext
 TNOI 4 Table with no index sybsystemprocs..spt_jdatatype_info sybsystemprocs..spt_jdatatype_info
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info_ext  
 MGTP 3 Grant to public sybsystemprocs..spt_jdatatype_info  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check @@error after delete 16
 MNER 3 No Error Check should check @@error after insert 18
 MUCO 3 Useless Code Useless Brackets 88
 QISO 3 Set isolation level 14
 QNAO 3 Not using ANSI Outer Join 39
 QNAO 3 Not using ANSI Outer Join 55
 QNAO 3 Not using ANSI Outer Join 70
 QNAO 3 Not using ANSI Outer Join 82
 QUNI 3 Check Use of 'union' vs 'union all' 19
 MTR1 2 Metrics: Comments Ratio Comments: 9% 6
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 3 = 2dec - 1exi + 2 6
 MTR3 2 Metrics: Query Complexity Complexity: 39 6

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..spt_datatype_info_ext  
reads table sybsystemprocs..spt_jdatatype_info  
writes table tempdb..#tmp_datatype_info (1)