| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_oledb_datatype_info | | 31 Aug 14 | Defects Dependencies |
1 2 create 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, syscolumns c, 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, syscolumns c, 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 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..spt_datatype_info_ext reads table sybsystemprocs..spt_sybdrv reads table sybsystemprocs..spt_datatype_info reads table sybsystemprocs..systypes reads table sybsystemprocs..syscolumns read_writes table tempdb..#oledb_results_table (1) |
