| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_datatype_info | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* 4 ** Sccsid = "%Z% generic/sproc/%M% %I% %G%" 5 ** 6 ** History: 7 ** 10.0 steven 1.1 07/13/93 sproc/src/datatype_info 8 ** Ported from MS catalog SP's 9 ** 10 ** Implementation Notes: 11 ** The messiness of 'sp_data_type_info' was to get around the 12 ** problem of returning the correct lengths for user defined types. The 13 ** join on the type name ensures all user defined types are returned, but 14 ** this puts a null in the data_type column. By forcing an embedded 15 ** select and correlating it with the current row in systypes, we get the 16 ** correct data_type mapping even for user defined types. 17 */ 18 19 create procedure sp_datatype_info 20 @data_type int = 0 /* Provide datatype_info for type # */ 21 as 22 if (select @data_type) = 0 23 begin 24 select /* Real SQL Server data types */ 25 type_name = 26 case 27 when t.usertype in (44, 45, 46) 28 then "unsigned " + substring(t.name, 29 charindex("u", t.name) + 1, 30 charindex("t", t.name)) 31 else 32 t.name 33 end, 34 d.data_type, 35 "precison" = isnull(d.data_precision, 36 convert(int, t.length)), 37 d.literal_prefix, 38 d.literal_suffix, 39 e.create_params, 40 d.nullable, 41 d.case_sensitive, 42 d.searchable, 43 d.unsigned_attribute, 44 d.money, 45 d.auto_increment, 46 d.local_type_name, 47 d.minimum_scale, 48 d.maximum_scale, 49 d.sql_data_type, 50 d.sql_datetime_sub, 51 d.num_prec_radix, 52 d.interval_precision 53 from sybsystemprocs.dbo.spt_datatype_info d, 54 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 55 where 56 d.ss_dtype = t.type 57 and t.usertype *= e.user_type 58 /* restrict results to "real" datatypes */ 59 and t.name not in ("nchar", "nvarchar", 60 "sysname", "longsysname", "timestamp", 61 "datetimn", "floatn", "intn", "moneyn", 62 "uintn") 63 and t.usertype < 100 /* No user defined types */ 64 UNION 65 select /* SQL Server user data types */ 66 type_name = t.name, 67 d.data_type, 68 "precison" = isnull(d.data_precision, 69 convert(int, t.length)), 70 d.literal_prefix, 71 d.literal_suffix, 72 e.create_params, 73 d.nullable, 74 d.case_sensitive, 75 d.searchable, 76 d.unsigned_attribute, 77 d.money, 78 d.auto_increment, 79 t.name, 80 d.minimum_scale, 81 d.maximum_scale, 82 d.sql_data_type, 83 d.sql_datetime_sub, 84 d.num_prec_radix, 85 d.interval_precision 86 from sybsystemprocs.dbo.spt_datatype_info d, 87 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 88 where 89 d.ss_dtype = t.type 90 and t.usertype *= e.user_type 91 /* 92 ** Restrict to user defined types (value > 100) 93 ** and Sybase user defined types (listed) 94 */ 95 and (t.name in ("nchar", "nvarchar", 96 "sysname", "longsysname", "timestamp") 97 or t.usertype >= 100) /* User defined types */ 98 order by d.data_type, type_name 99 end 100 else 101 begin 102 select /* Real SQL Server data types */ 103 type_name = 104 case 105 when t.usertype in (44, 45, 46) 106 then "unsigned " + substring(t.name, 107 charindex("u", t.name) + 1, 108 charindex("t", t.name)) 109 else 110 t.name 111 end, 112 d.data_type, 113 "precison" = isnull(d.data_precision, 114 convert(int, t.length)), 115 d.literal_prefix, 116 d.literal_suffix, 117 e.create_params, 118 d.nullable, 119 d.case_sensitive, 120 d.searchable, 121 d.unsigned_attribute, 122 d.money, 123 d.auto_increment, 124 d.local_type_name, 125 d.sql_data_type, 126 d.sql_datetime_sub, 127 d.num_prec_radix, 128 d.interval_precision 129 from sybsystemprocs.dbo.spt_datatype_info d, 130 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 131 where 132 data_type = @data_type 133 and d.ss_dtype = t.type 134 and t.usertype *= e.user_type 135 /* restrict results to "real" datatypes */ 136 and t.name not in ("nchar", "nvarchar", 137 "sysname", "longsysname", "timestamp", 138 "datetimn", "floatn", "intn", "moneyn", 139 "uintn") 140 and t.usertype < 100 /* No user defined types */ 141 UNION 142 select /* SQL Server and user data types */ 143 type_name = t.name, 144 d.data_type, 145 "precison" = isnull(d.data_precision, 146 convert(int, t.length)), 147 d.literal_prefix, 148 d.literal_suffix, 149 e.create_params, 150 d.nullable, 151 d.case_sensitive, 152 d.searchable, 153 d.unsigned_attribute, 154 d.money, 155 d.auto_increment, 156 t.name, 157 d.sql_data_type, 158 d.sql_datetime_sub, 159 d.num_prec_radix, 160 d.interval_precision 161 from sybsystemprocs.dbo.spt_datatype_info d, 162 sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t 163 where 164 data_type = @data_type 165 and d.ss_dtype = t.type 166 and t.usertype *= e.user_type 167 /* 168 ** Restrict to user defined types (value > 100) 169 ** and Sybase user defined types (listed) 170 */ 171 and (t.name in ("nchar", "nvarchar", 172 "sysname", "longsysname", "timestamp") 173 or t.usertype >= 100) /* User defined types */ 174 order by type_name 175 end 176 return (0) 177
exec sp_procxmode 'sp_datatype_info', 'AnyMode' go Grant Execute on sp_datatype_info to public go
| RESULT SETS | |
| sp_datatype_info_rset_002 | |
| sp_datatype_info_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..spt_datatype_info reads table sybsystemprocs..systypes reads table sybsystemprocs..spt_datatype_info_ext |
