DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_datatype_info  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_oledb_datatype_info
3         @data_type int = 0, /* Provide datatype_info for type # */
4         @best_match bit = 0
5     as
6         declare @startedInTransaction bit
7     
8         if (@@trancount > 0)
9             select @startedInTransaction = 1
10        else
11            select @startedInTransaction = 0
12    
13        if (@startedInTransaction = 1)
14            save transaction oledb_keep_temptable_tx
15    
16        create table #oledb_results_table
17        (
18            TYPE_NAME varchar(255) null,
19            DATA_TYPE smallint null,
20            --  COLUMN_SIZE		int  null,
21            COLUMN_SIZE unsigned int null,
22            LITERAL_PREFIX varchar(32) null,
23            LITERAL_SUFFIX varchar(32) null,
24            CREATE_PARAMS varchar(32) null,
25            IS_NULLABLE bit not null,
26            CASE_SENSITIVE bit not null,
27            --  SEARCHABLE		int null,
28            SEARCHABLE unsigned int null,
29            UNSIGNED_ATTRIBUTE bit not null,
30            FIXED_PREC_SCALE bit not null,
31            AUTO_UNIQUE_VALUE bit not null,
32            LOCAL_TYPE_NAME varchar(128) null,
33            MINIMUM_SCALE smallint null,
34            MAXIMUM_SCALE smallint null,
35            GUID varchar(32) null,
36            TYPELIB varchar(32) null,
37            VERSION varchar(32) null,
38            IS_LONG bit not null,
39            BEST_MATCH bit not null,
40            IS_FIXEDLENGTH bit not null
41    
42        )
43    
44        insert #oledb_results_table
45        select /* Real SQL Server data types */
46            TYPE_NAME = case
47                when t.usertype in (44, 45, 46)
48                then "unsigned " + substring(t.name,
49                    charindex("u", t.name) + 1,
50                    charindex("t", t.name))
51                else
52                    t.name
53            end,
54            DATA_TYPE = d.ss_dtype,
55            COLUMN_SIZE = isnull(d.data_precision,
56                convert(int, t.length)),
57            LITERAL_PREFIX = d.literal_prefix,
58            LITERAL_SUFFIX = d.literal_suffix,
59            CREATE_PARAMS = e.create_params,
60            IS_NULLABLE = convert(bit, d.nullable),
61            CASE_SENSITIVE = d.case_sensitive,
62            SEARCHABLE = d.searchable + 1,
63            UNSIGNED_ATTRIBUTE = isnull(d.unsigned_attribute, convert(bit, 1)),
64            FIXED_PREC_SCALE = convert(bit, d.money),
65            AUTO_UNIQUE_VALUE = isnull(d.auto_increment, convert(bit, 0)),
66            LOCAL_TYPE_NAME = d.local_type_name,
67            MINIMUM_SCALE = d.minimum_scale,
68            MAXIMUM_SCALE = d.maximum_scale,
69            GUID = convert(varchar(255), null),
70            TYPE_LIB = convert(varchar(255), null),
71            VERSION = convert(varchar(255), null),
72            ISLONG = convert(bit, 0),
73            BEST_MATCH = convert(bit, 0),
74            IS_FIXEDLENGTH = convert(bit, 0)
75    
76        from sybsystemprocs.dbo.spt_datatype_info d,
77            sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
78        where
79            d.ss_dtype = t.type
80            and t.usertype *= e.user_type
81            /* restrict results to "real" datatypes */
82            and t.name not in ("nchar", "nvarchar",
83                "sysname", "longsysname", "timestamp",
84                "datetimn", "floatn", "intn", "uintn", "moneyn",
85                "extended type", "bigdatetimen", "bigtimen")
86            and t.usertype < 100 /* No user defined types */
87        UNION
88        select /* SQL Server user data types */
89            TYPE_NAME = t.name,
90            DATA_TYPE = d.ss_dtype,
91            COLUMN_SIZE = isnull(d.data_precision,
92                convert(int, t.length)),
93            LITERAL_PREFIX = d.literal_prefix,
94            LITERAL_SUFFIX = d.literal_suffix,
95            CREATE_PARAMS = e.create_params,
96            IS_NULLABLE = convert(bit, d.nullable),
97            CASE_SENSITIVE = d.case_sensitive,
98            SEARCHABLE = d.searchable + 1,
99            UNSIGNED_ATTRIBUTE = isnull(d.unsigned_attribute, convert(bit, 1)),
100           FIXED_PREC_SCALE = convert(bit, d.money),
101           AUTO_UNIQUE_VALUE = isnull(d.auto_increment, convert(bit, 0)),
102           LOCAL_TYPE_NAME = t.name,
103           MINIMUM_SCALE = d.minimum_scale,
104           MAXIMUM_SCALE = d.maximum_scale,
105           GUID = convert(varchar(255), null),
106           TYPE_LIB = convert(varchar(255), null),
107           VERSION = convert(varchar(255), null),
108           ISLONG = convert(bit, 0),
109           BEST_MATCH = convert(bit, 0),
110           IS_FIXEDLENGTH = convert(bit, 0)
111       from sybsystemprocs.dbo.spt_datatype_info d,
112           sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
113       where
114           d.ss_dtype = t.type
115           and t.usertype *= e.user_type
116           /* 
117           ** Restrict to user defined types (value > 100)
118           ** and Sybase user defined types (listed)
119           */
120           and (t.name in ("nchar", "nvarchar",
121                   "sysname", "timestamp")
122               and t.usertype < 100) /* User defined types */
123       order by TYPE_NAME
124   
125       delete from #oledb_results_table where DATA_TYPE = 0
126       update #oledb_results_table set o.DATA_TYPE = m.data_type from
127           sybsystemprocs.dbo.spt_sybdrv m, #oledb_results_table o
128       where o.TYPE_NAME = m.type_name
129   
130       update #oledb_results_table set IS_FIXEDLENGTH = 1 where DATA_TYPE not in (37, 39, 35, 155) and TYPE_NAME not in ("varchar", "varbinary", "univarchar", "text", "unitext", "image", "nvarchar")
131       update #oledb_results_table set IS_LONG = 1 where DATA_TYPE in (34, 35, 174) or TYPE_NAME in ("text", "image", "unitext")
132   
133   
134   
135       update #oledb_results_table set o.DATA_TYPE = m.data_type from
136           sybsystemprocs.dbo.spt_sybdrv m, #oledb_results_table o
137       where o.TYPE_NAME not in (select type_name from sybsystemprocs.dbo.spt_sybdrv) and
138           o.DATA_TYPE = m.tds_type
139   
140       update #oledb_results_table set BEST_MATCH = 1 where DATA_TYPE not in (6, 128, 129, 130, 131, 135)
141       update #oledb_results_table set BEST_MATCH = 1 where TYPE_NAME = "money"
142       update #oledb_results_table set BEST_MATCH = 1 where TYPE_NAME = "char"
143       update #oledb_results_table set BEST_MATCH = 1 where TYPE_NAME = "unichar"
144       update #oledb_results_table set BEST_MATCH = 1 where TYPE_NAME = "numeric"
145       update #oledb_results_table set BEST_MATCH = 1 where TYPE_NAME = "binary"
146       update #oledb_results_table set BEST_MATCH = 1 where TYPE_NAME = "datetime"
147   
148       if (@data_type = 0)
149           /* Adaptive Server has expanded all '*' elements in the following statement */ select #oledb_results_table.TYPE_NAME, #oledb_results_table.DATA_TYPE, #oledb_results_table.COLUMN_SIZE, #oledb_results_table.LITERAL_PREFIX, #oledb_results_table.LITERAL_SUFFIX, #oledb_results_table.CREATE_PARAMS, #oledb_results_table.IS_NULLABLE, #oledb_results_table.CASE_SENSITIVE, #oledb_results_table.SEARCHABLE, #oledb_results_table.UNSIGNED_ATTRIBUTE, #oledb_results_table.FIXED_PREC_SCALE, #oledb_results_table.AUTO_UNIQUE_VALUE, #oledb_results_table.LOCAL_TYPE_NAME, #oledb_results_table.MINIMUM_SCALE, #oledb_results_table.MAXIMUM_SCALE, #oledb_results_table.GUID, #oledb_results_table.TYPELIB, #oledb_results_table.VERSION, #oledb_results_table.IS_LONG, #oledb_results_table.BEST_MATCH, #oledb_results_table.IS_FIXEDLENGTH from #oledb_results_table order by DATA_TYPE, TYPE_NAME
150       else
151   
152   
153       if (@best_match = 0)
154           /* Adaptive Server has expanded all '*' elements in the following statement */ select #oledb_results_table.TYPE_NAME, #oledb_results_table.DATA_TYPE, #oledb_results_table.COLUMN_SIZE, #oledb_results_table.LITERAL_PREFIX, #oledb_results_table.LITERAL_SUFFIX, #oledb_results_table.CREATE_PARAMS, #oledb_results_table.IS_NULLABLE, #oledb_results_table.CASE_SENSITIVE, #oledb_results_table.SEARCHABLE, #oledb_results_table.UNSIGNED_ATTRIBUTE, #oledb_results_table.FIXED_PREC_SCALE, #oledb_results_table.AUTO_UNIQUE_VALUE, #oledb_results_table.LOCAL_TYPE_NAME, #oledb_results_table.MINIMUM_SCALE, #oledb_results_table.MAXIMUM_SCALE, #oledb_results_table.GUID, #oledb_results_table.TYPELIB, #oledb_results_table.VERSION, #oledb_results_table.IS_LONG, #oledb_results_table.BEST_MATCH, #oledb_results_table.IS_FIXEDLENGTH from #oledb_results_table where DATA_TYPE = @data_type
155       else /* Adaptive Server has expanded all '*' elements in the following statement */
156           select #oledb_results_table.TYPE_NAME, #oledb_results_table.DATA_TYPE, #oledb_results_table.COLUMN_SIZE, #oledb_results_table.LITERAL_PREFIX, #oledb_results_table.LITERAL_SUFFIX, #oledb_results_table.CREATE_PARAMS, #oledb_results_table.IS_NULLABLE, #oledb_results_table.CASE_SENSITIVE, #oledb_results_table.SEARCHABLE, #oledb_results_table.UNSIGNED_ATTRIBUTE, #oledb_results_table.FIXED_PREC_SCALE, #oledb_results_table.AUTO_UNIQUE_VALUE, #oledb_results_table.LOCAL_TYPE_NAME, #oledb_results_table.MINIMUM_SCALE, #oledb_results_table.MAXIMUM_SCALE, #oledb_results_table.GUID, #oledb_results_table.TYPELIB, #oledb_results_table.VERSION, #oledb_results_table.IS_LONG, #oledb_results_table.BEST_MATCH, #oledb_results_table.IS_FIXEDLENGTH from #oledb_results_table where DATA_TYPE = @data_type
157               and @best_match = 1
158   
159   
160       if (@startedInTransaction = 1)
161           rollback transaction oledb_keep_temptable_tx
162   
163       return (0)
164   


exec sp_procxmode 'sp_oledb_datatype_info', 'AnyMode'
go

Grant Execute on sp_oledb_datatype_info to public
go
RESULT SETS
sp_oledb_datatype_info_rset_003
sp_oledb_datatype_info_rset_002
sp_oledb_datatype_info_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 79
 QJWI 5 Join or Sarg Without Index 80
 QJWI 5 Join or Sarg Without Index 114
 QJWI 5 Join or Sarg Without Index 115
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 126
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 135
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 86
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 122
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 138
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 154
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 156
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info_ext sybsystemprocs..spt_datatype_info_ext
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info_ext  
 MGTP 3 Grant to public sybsystemprocs..spt_sybdrv  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check @@error after insert 44
 MNER 3 No Error Check should check @@error after delete 125
 MNER 3 No Error Check should check @@error after update 126
 MNER 3 No Error Check should check @@error after update 130
 MNER 3 No Error Check should check @@error after update 131
 MNER 3 No Error Check should check @@error after update 135
 MNER 3 No Error Check should check @@error after update 140
 MNER 3 No Error Check should check @@error after update 141
 MNER 3 No Error Check should check @@error after update 142
 MNER 3 No Error Check should check @@error after update 143
 MNER 3 No Error Check should check @@error after update 144
 MNER 3 No Error Check should check @@error after update 145
 MNER 3 No Error Check should check @@error after update 146
 MUCO 3 Useless Code Useless Brackets 8
 MUCO 3 Useless Code Useless Brackets 13
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 153
 MUCO 3 Useless Code Useless Brackets 160
 MUCO 3 Useless Code Useless Brackets 163
 QCRS 3 Conditional Result Set 149
 QCRS 3 Conditional Result Set 154
 QCRS 3 Conditional Result Set 156
 QJWT 3 Join or Sarg Without Index on temp table 137
 QJWT 3 Join or Sarg Without Index on temp table 138
 QNAJ 3 Not using ANSI Inner Join 126
 QNAJ 3 Not using ANSI Inner Join 135
 QNAO 3 Not using ANSI Outer Join 76
 QNAO 3 Not using ANSI Outer Join 111
 QNUA 3 Should use Alias: Table sybsystemprocs..spt_sybdrv 137
 QPNC 3 No column in condition 157
 QTLO 3 Top-Level OR 131
 QUNI 3 Check Use of 'union' vs 'union all' 45
 MRST 2 Result Set Marker 149
 MRST 2 Result Set Marker 154
 MRST 2 Result Set Marker 156
 MTR1 2 Metrics: Comments Ratio Comments: 7% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 6dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 89 2

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..spt_datatype_info_ext  
read_writes table tempdb..#oledb_results_table (1) 
writes table sybsystemprocs..sp_oledb_datatype_info_rset_001 
reads table sybsystemprocs..spt_sybdrv  
writes table sybsystemprocs..sp_oledb_datatype_info_rset_002 
writes table sybsystemprocs..sp_oledb_datatype_info_rset_003 
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..spt_datatype_info