1
2
3
4
5
6 /*
7 ** Sccsid = "%Z% generic/sproc/%M% %I% %G%"
8 **
9 ** History:
10 ** mm/yy Author Comments
11 ** 12/06 Meena Ramakrishnan Changes to conform to the ODBC API
12 ** of returning the system types in any
13 ** given sql data type first before the user defined types
14 **
15 ** This stored procedure has been ported from the ASE stored procedure sp_datatype_info
16 **
17 ** 10.0 steven 1.1 07/13/93 sproc/src/datatype_info
18 ** Ported from MS catalog SP's
19 **
20 ** Implementation Notes:
21 ** The messiness of 'sp_odbc_data_type_info' was to get around the
22 ** problem of returning the correct lengths for user defined types. The
23 ** join on the type name ensures all user defined types are returned, but
24 ** this puts a null in the data_type column. By forcing an embedded
25 ** select and correlating it with the current row in systypes, we get the
26 ** correct data_type mapping even for user defined types.
27 **
28 ** parameters: data_type of type int
29 */
30
31 create procedure sp_odbc_datatype_info
32 @data_type int = 0, /* Provide datatype_info for type # */
33 @odbc_ver int = 3 /* Provide result set for ODBC 3.0 API # */
34 as
35
36 declare @startedInTransaction bit
37
38 if (@@trancount > 0)
39 select @startedInTransaction = 1
40 else
41 select @startedInTransaction = 0
42
43
44 if (@startedInTransaction = 1)
45 save transaction odbc_keep_temptable_tx
46
47 set @data_type =
48 case @data_type
49 when null then 0
50 when 91 then 9
51 when 92 then 10
52 when 93 then 11
53 else @data_type
54 end
55
56
57 select
58 TYPE_NAME =
59 cast(
60 case t.name
61 when 'usmallint' then 'unsigned smallint'
62 when 'uint' then 'unsigned int'
63 when 'ubigint' then 'unsigned bigint'
64 else t.name
65 end
66 as varchar(30)),
67 DATA_TYPE =
68 cast(
69 case
70 when d.data_type = 9 and @odbc_ver = 3 then 91
71 when d.data_type = 10 and @odbc_ver = 3 then 92
72 when d.data_type = 11 and @odbc_ver = 3 then 93
73 when t.name = 'unichar' and @odbc_ver in (2, 3) then - 8
74 when t.name = 'univarchar' and @odbc_ver in (2, 3) then - 9
75 else d.data_type
76 end
77 as smallint),
78 COLUMN_SIZE = cast(isnull(d.data_precision, t.length / case when t.name in ('univarchar', 'unichar') then 2 else 1 end) as int),
79 LITERAL_PREFIX = cast(d.literal_prefix as varchar(30)),
80 LITERAL_SUFFIX = cast(d.literal_suffix as varchar(30)),
81 CREATE_PARAMS = cast(case when e.create_params = 'max length' or d.data_type in (1, 12) then 'length' else e.create_params end as varchar(30)),
82 NULLABLE = cast(d.nullable as smallint),
83 CASE_SENSITIVE = cast(d.case_sensitive as smallint),
84 SEARCHABLE = cast(d.searchable as smallint),
85 UNSIGNED_ATTRIBUTE = cast(d.unsigned_attribute as smallint),
86 FIXED_PREC_SCALE = cast(d.money as smallint),
87 AUTO_UNIQUE_VALUE = cast(d.auto_increment as smallint),
88 LOCAL_TYPE_NAME = cast(case when t.name = 'timestamp' then 'timestamp' else d.local_type_name end as varchar(30)),
89 MINIMUM_SCALE = cast(case when d.data_type in (10, 11) then 0 else d.minimum_scale end as smallint),
90 MAXIMUM_SCALE = cast(case when d.data_type in (10, 11) then 3 else d.minimum_scale end as smallint),
91 SQL_DATA_TYPE =
92 cast(
93 case
94 when d.data_type in (9, 10, 11) then 9
95 when t.name = 'unichar' then - 8
96 when t.name = 'univarchar' then - 9
97 else isnull(d.sql_data_type, d.data_type + d.aux)
98 end
99 as smallint),
100 SQL_DATETIME_SUB =
101 cast(case d.data_type
102 when 9 then 1
103 when 10 then 2
104 when 11 then 3
105 else d.sql_datetime_sub
106 end
107 as smallint),
108 NUM_PREC_RADIX = cast(case when d.data_type in (2, 3, 4, 5, 6, 7, - 7, - 6) then 10 else d.num_prec_radix end as int),
109 INTERVAL_PRECISION = cast(d.interval_precision as smallint)
110 from sybsystemprocs.dbo.spt_datatype_info d
111 join systypes t
112 on t.type = d.ss_dtype
113 left join sybsystemprocs.dbo.spt_datatype_info_ext e
114 on e.user_type = t.usertype
115 where d.data_type = case @data_type when 0 then d.data_type else @data_type end
116 and t.name not in ('longsysname', 'datetimn', 'floatn', 'intn', 'moneyn', 'uintn') -- not real datatypes
117 order by
118 DATA_TYPE,
119 t.usertype,
120 TYPE_NAME
121
122 if (@startedInTransaction = 1)
123 rollback transaction odbc_keep_temptable_tx
124
125 return (0)
126
exec sp_procxmode 'sp_odbc_datatype_info', 'AnyMode'
go
Grant Execute on sp_odbc_datatype_info to public
go
QJWI 5 Join or Sarg Without Index 
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int
MGTP 3 Grant to public sybsystemprocs..sp_odbc_datatype_info
MRST 2 Result Set Marker