| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_oledb_columns | | 31 Aug 14 | Defects Dependencies |
1 2 3 /* Sccsid = "%Z% generic/sproc/%M% %I% %G% " */ 4 /* 10.0 07/20/93 sproc/columns */ 5 6 7 /* This is the version for servers which support UNION */ 8 9 /* This routine is intended for support of oledb connectivity. Under no 10 ** circumstances should changes be made to this routine unless they are 11 ** to fix oledb related problems. All other users are at there own risk! 12 ** 13 ** Please be aware that any changes made to this file (or any other oledb 14 ** support routine) will require Sybase to recertify the SQL server as 15 ** oledb compliant. This process is currently being managed internally 16 ** by the "Interoperability Engineering Technology Solutions Group" here 17 ** within Sybase. 18 */ 19 20 CREATE PROCEDURE sp_oledb_columns( 21 @table_name varchar(771) = null, 22 @table_owner varchar(32) = null, 23 @table_qualifier varchar(32) = null, 24 @column_name varchar(771) = null, 25 @is_ado int = 1, 26 @stripblanks int = 1) 27 AS 28 declare @full_table_name varchar(1543) 29 declare @table_id int 30 declare @char_bin_types varchar(32) 31 declare @startedInTransaction bit 32 33 if (@@trancount > 0) 34 select @startedInTransaction = 1 35 else 36 select @startedInTransaction = 0 37 38 set transaction isolation level 1 39 40 if (@startedInTransaction = 1) 41 save transaction oledb_keep_temptable_tx 42 43 /* character and binary datatypes */ 44 select @char_bin_types = 45 char(47) + char(39) + char(45) + char(37) + char(35) + char(34) 46 create table #results_table 47 (TABLE_CATALOG varchar(32) null, 48 TABLE_SCHEMA varchar(32) null, 49 TABLE_NAME varchar(255) null, 50 COLUMN_NAME varchar(255) null, 51 COLUMN_GUID varchar(36) null, 52 COLUMN_PROPID int null, 53 ORDINAL_POSITION int null, 54 COLUMN_HASDEFAULT bit default 1 not null, 55 COLUMN_DEFAULT varchar(255) null, 56 COLUMN_FLAGS int null, 57 usertype int null, 58 IS_NULLABLE bit default 1 not null, 59 DATA_TYPE smallint null, 60 TYPE_GUID varchar(36) null, 61 CHARACTER_MAXIMUM_LENGTH int null, 62 CHARACTER_OCTET_LENGTH int null, 63 NUMERIC_PRECISION smallint null, 64 NUMERIC_PRECISION_RADIX smallint null, 65 TYPE_NAME varchar(255) null, 66 NUMERIC_SCALE smallint null, 67 DATETIME_PRECISION int null, 68 CHARACTER_SET_CATALOG varchar(32) null, 69 CHARACTER_SET_SCHEMA varchar(32) null, 70 CHARACTER_SET_NAME varchar(255) null, 71 COLLATION_CATALOG varchar(32) null, 72 COLLATION_SCHEMA varchar(32) null, 73 COLLATION_NAME varchar(32) null, 74 DOMAIN_CATALOG varchar(32) null, 75 DOMAIN_SCHEMA varchar(32) null, 76 DOMAIN_NAME varchar(32) null, 77 DESCRIPTION varchar(32) null, 78 tds_type smallint null, 79 id int null, 80 col_len int null, 81 sys_type smallint null, 82 row_id numeric(10) identity) 83 84 if @column_name is null /* If column name not supplied, match all */ 85 select @column_name = '%' 86 87 /* Check if the current database is the same as the one provided */ 88 if @table_qualifier is not null 89 begin 90 91 if db_name() != @table_qualifier 92 begin /* 93 ** If qualifier doesn't match current database 94 ** force a no-row selection 95 */ 96 goto SelectClause 97 end 98 end 99 100 if @table_name is null 101 begin /* If table name not supplied, match all */ 102 select @table_name = '%' 103 end 104 105 if @table_owner is null 106 begin /* If unqualified table name */ 107 SELECT @full_table_name = @table_name 108 end 109 else 110 begin /* Qualified table name */ 111 SELECT @full_table_name = @table_owner + '.' + @table_name 112 end 113 114 /* Get Object ID */ 115 SELECT @table_id = object_id(@full_table_name) 116 117 118 /* If the table name parameter is valid, get the information */ 119 if ((charindex('%', @full_table_name) = 0) and 120 (charindex('_', @full_table_name) = 0) and 121 @table_id != 0) 122 begin 123 124 insert into #results_table 125 SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */ 126 TABLE_CATALOG = DB_NAME(), 127 TABLE_SCHEMA = USER_NAME(o.uid), 128 TABLE_NAME = o.name, 129 COLUMN_NAME = c.name, 130 COLUMN_GUID = convert(varchar(36), null), 131 COLUMN_PROPID = convert(int, null), 132 ORDINAL_POSITION = convert(int, c.colid), 133 COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1), 134 COLUMN_DEFAULT = convert(varchar(254), null), 135 /*CT: IsNullable 0x20 (c.status&8 * 4), 136 MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable 137 IsFixedLength 0x10 - yes 138 IsLong 0x80 - no 139 */ 140 COLUMN_FLAGS = convert(int, c.status & 8) * 12 + 16, 141 usertype = t.usertype, 142 IS_NULLABLE = convert(bit, c.status & 8), 143 DATA_TYPE = convert(smallint, m.data_type), 144 TYPE_GUID = convert(varchar(36), null), 145 CHARACTER_MAXIMUM_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */ 146 CHARACTER_OCTET_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */ 147 /* CT: MONEYN, INTN, DATETIMN and FLOATN will all be included */ 148 NUMERIC_PRECISION = isnull(convert(smallint, c.prec), 149 isnull(convert(smallint, d.data_precision), 150 convert(smallint, c.length))) 151 + isnull(d.aux, convert(smallint, 152 ascii(substring("???AAAFFFCKFOLS", 153 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 154 155 NUMERIC_PRECISION_RADIX = d.numeric_radix, 156 TYPE_NAME = 157 case 158 when convert(bit, (c.status & 0x80)) = 1 then 159 rtrim(substring(d.type_name, 160 1 + isnull(d.aux, 161 ascii(substring('III<<<MMMI<<A<A', 162 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 163 1)) - 60), 18)) + ' identity' 164 else 165 rtrim(substring(d.type_name, 166 1 + isnull(d.aux, 167 ascii(substring('III<<<MMMI<<A<A', 168 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 169 1)) - 60), 18)) 170 end, 171 NUMERIC_SCALE = convert(smallint, null), /*CT: only for decimal and numeric in oledb*/ 172 /*CT: in ss_dtype 111, 109, 38, 110, only 111 has non null value for this item 173 it is the datetime type scale, UI4 type*/ 174 DATETIME_PRECISION = case 175 when d.ss_dtype = 111 /*CT: also the type is changed to UI4*/ 176 then 177 isnull(convert(int, c.scale), 178 convert(int, d.numeric_scale)) 179 + convert(int, 180 isnull(d.aux, 181 ascii(substring("<<<<<<<<<<<<<<?", 182 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 183 1)) - 60)) 184 else 185 convert(int, null) 186 end, 187 188 CHARACTER_SET_CATALOG = null, 189 CHARACTER_SET_SCHEMA = null, 190 CHARACTER_SET_NAME = null, 191 COLLATION_CATALOG = null, 192 COLLATION_SCHEMA = null, 193 COLLATION_NAME = null, 194 DOMAIN_CATALOG = null, 195 DOMAIN_SCHEMA = null, 196 DOMAIN_NAME = null, 197 DESCRIPTION = null, /* Description are NULL */ 198 tds_type = c.type, 199 id = c.id, 200 col_len = c.length, 201 sys_type = t.type 202 203 FROM 204 syscolumns c, 205 sysobjects o, 206 sybsystemprocs.dbo.spt_datatype_info d, 207 systypes t, 208 sybsystemprocs.dbo.spt_sybdrv m 209 WHERE 210 o.id = @table_id 211 AND c.id = o.id 212 /* 213 ** We use syscolumn.usertype instead of syscolumn.type 214 ** to do join with systypes.usertype. This is because 215 ** for a column which allows null, type stores its 216 ** Server internal datatype whereas usertype still 217 ** stores its user defintion datatype. For an example, 218 ** a column of type 'decimal NULL', its usertype = 26, 219 ** representing decimal whereas its type = 106 220 ** representing decimaln. nullable in the select list 221 ** already tells user whether the column allows null. 222 ** In the case of user defining datatype, this makes 223 ** more sense for the user. 224 */ 225 AND c.usertype = t.usertype 226 AND t.type = d.ss_dtype 227 AND c.name like @column_name 228 -- AND d.ss_dtype IN (111, 109, 38, 110) /* Just *N types */ 229 AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */ 230 AND c.usertype < 100 /* No user defined types */ 231 AND m.type_name = 232 rtrim(substring(d.type_name, 233 1 + isnull(d.aux, 234 ascii(substring("III<<<MMMI<<A<A", 235 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 236 1)) - 60), 18)) 237 UNION 238 SELECT /* All other types including user data types */ 239 TABLE_CATALOG = DB_NAME(), 240 TABLE_SCHEMA = USER_NAME(o.uid), 241 TABLE_NAME = o.name, 242 COLUMN_NAME = c.name, 243 COLUMN_GUID = convert(varchar(36), null), 244 COLUMN_PROPID = convert(int, null), 245 ORDINAL_POSITION = convert(int, c.colid), 246 COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1), 247 COLUMN_DEFAULT = convert(varchar(254), null), 248 /*CT: IsNullable 0x20 (c.status&8 * 4), 249 MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable 250 IsFixedLength 0x10 - yes 251 IsLong 0x80 - no 252 */ 253 COLUMN_FLAGS = convert(int, c.status & 8) * 12 + 254 case when c.type in (37, 39, 155, 34, 35, 174) then 0 255 when c.type in (47) and (convert(int, c.status & 8) = 1) then 0 256 else 16 end + 257 case when c.type in (34, 35, 174) then 128 /*text, image and unitext type*/ 258 else 0 end, 259 usertype = t.usertype, 260 IS_NULLABLE = convert(bit, c.status & 8), 261 DATA_TYPE = convert(smallint, m.data_type), 262 TYPE_GUID = convert(varchar(36), null), 263 /*CT: only for char, binary and bit in oledb, see spec*/ 264 CHARACTER_MAXIMUM_LENGTH = case 265 when c.type in (135, 155) 266 then 267 (isnull(convert(int, c.prec), 268 isnull(convert(int, d.data_precision), 269 convert(int, c.length))) 270 + isnull(d.aux, convert(int, 271 ascii(substring("???AAAFFFCKFOLS", 272 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))) / 2 273 when c.type in (47, 39, 45, 37, 35, 34, 50) 274 then 275 isnull(convert(int, c.prec), 276 isnull(convert(int, d.data_precision), 277 convert(int, c.length))) 278 + isnull(d.aux, convert(int, 279 ascii(substring("???AAAFFFCKFOLS", 280 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 281 else 282 convert(int, null) 283 end, 284 285 /* 286 ** if the datatype is of type CHAR or BINARY 287 ** then set char_octet_length to the same value 288 ** assigned in the "prec" column. 289 ** 290 ** The first part of the logic is: 291 ** 292 ** if(c.type is in (47, 39, 45, 37, 35, 34)) 293 ** set char_octet_length = prec; 294 ** if (c.type is in (135, 155) 295 ** set char_octet_length = prec * 2; 296 ** else 297 ** set char_octet_length = null; 298 */ 299 CHARACTER_OCTET_LENGTH = case 300 when c.type in (47, 39, 45, 37, 35, 34, 50) 301 then /*same size as the CHARACTER_MAXIMUM_LENGTH */ 302 isnull(convert(int, c.prec), 303 isnull(convert(int, d.data_precision), 304 convert(int, c.length))) 305 + isnull(d.aux, convert(int, 306 ascii(substring("???AAAFFFCKFOLS", 307 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 308 when c.type in (135, 155) 309 then /* CHARACTER_MAXIMUM_LENGTH * 2 for the unichar, univarchar*/ 310 isnull(convert(int, c.prec), 311 isnull(convert(int, d.data_precision), 312 convert(int, c.length))) 313 + isnull(d.aux, convert(int, 314 ascii(substring("???AAAFFFCKFOLS", 315 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 316 else 317 convert(int, null) 318 end, 319 NUMERIC_PRECISION = case 320 when c.type in (47, 39, 45, 37, 35, 34, 135, 155, 50, 58, 61, 123, 147, 174) 321 then convert(smallint, null) 322 else 323 isnull(convert(smallint, c.prec), 324 isnull(convert(smallint, d.data_precision), 325 convert(smallint, c.length))) 326 + isnull(d.aux, convert(smallint, 327 ascii(substring("???AAAFFFCKFOLS", 328 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 329 end, 330 NUMERIC_PRECISION_RADIX = d.numeric_radix, 331 TYPE_NAME = 332 case 333 when convert(bit, (c.status & 0x80)) = 1 then 334 rtrim(substring(d.type_name, 335 1 + isnull(d.aux, 336 ascii(substring('III<<<MMMI<<A<A', 337 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 338 1)) - 60), 18)) + ' identity' 339 else 340 rtrim(substring(d.type_name, 341 1 + isnull(d.aux, 342 ascii(substring('III<<<MMMI<<A<A', 343 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 344 1)) - 60), 18)) 345 end, 346 347 NUMERIC_SCALE = case 348 when c.type in (63, 108, 55, 106) then 349 isnull(convert(smallint, c.scale), 350 convert(smallint, d.numeric_scale)) + 351 convert(smallint, isnull(d.aux, 352 ascii(substring("<<<<<<<<<<<<<<?", 353 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 354 1)) - 60)) 355 else 356 convert(smallint, null) 357 end, 358 DATETIME_PRECISION = case 359 when c.type in (58, 61, 123, 147, 111) then 360 isnull(convert(int, c.scale), 361 convert(int, d.numeric_scale)) + 362 convert(int, isnull(d.aux, 363 ascii(substring("<<<<<<<<<<<<<<?", 364 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 365 1)) - 60)) 366 else 367 convert(int, null) 368 end, 369 CHARACTER_SET_CATALOG = null, 370 CHARACTER_SET_SCHEMA = null, 371 CHARACTER_SET_NAME = null, 372 COLLATION_CATALOG = null, 373 COLLATION_SCHEMA = null, 374 COLLATION_NAME = null, 375 DOMAIN_CATALOG = null, 376 DOMAIN_SCHEMA = null, 377 DOMAIN_NAME = null, 378 DESCRIPTION = null, /* Description are NULL */ 379 tds_type = c.type, 380 id = c.id, 381 col_len = c.length, 382 sys_type = t.type 383 384 385 FROM 386 syscolumns c, 387 sysobjects o, 388 sybsystemprocs.dbo.spt_datatype_info d, 389 systypes t, 390 sybsystemprocs.dbo.spt_sybdrv m 391 WHERE 392 o.id = @table_id 393 AND c.id = o.id 394 /* 395 ** We use syscolumn.usertype instead of syscolumn.type 396 ** to do join with systypes.usertype. This is because 397 ** for a column which allows null, type stores its 398 ** Server internal datatype whereas usertype still 399 ** stores its user defintion datatype. For an example, 400 ** a column of type 'decimal NULL', its usertype = 26, 401 ** representing decimal whereas its type = 106 402 ** representing decimaln. nullable in the select list 403 ** already tells user whether the column allows null. 404 ** In the case of user defining datatype, this makes 405 ** more sense for the user. 406 */ 407 AND c.usertype = t.usertype 408 /* 409 ** We need a equality join with 410 ** sybsystemprocs.dbo.spt_datatype_info here so that 411 ** there is only one qualified row returned from 412 ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding 413 ** duplicates. 414 */ 415 AND t.type = d.ss_dtype 416 AND c.name like @column_name 417 -- AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */ 418 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */ 419 420 OR c.usertype >= 100) /* User defined types */ 421 AND m.type_name = 422 rtrim(substring(d.type_name, 423 1 + isnull(d.aux, 424 ascii(substring("III<<<MMMI<<A<A", 425 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 426 1)) - 60), 18)) 427 428 ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 429 end 430 else 431 begin 432 /* 433 ** This block is for the case where there IS pattern 434 ** matching done on the table name. 435 */ 436 if @table_owner is null /* If owner not supplied, match all */ 437 select @table_owner = '%' 438 439 insert into #results_table 440 SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */ 441 TABLE_CATALOG = DB_NAME(), 442 TABLE_SCHEMA = USER_NAME(o.uid), 443 TABLE_NAME = o.name, 444 COLUMN_NAME = c.name, 445 COLUMN_GUID = convert(varchar(36), null), 446 COLUMN_PROPID = convert(int, null), 447 ORDINAL_POSITION = convert(int, c.colid), 448 COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1), 449 COLUMN_DEFAULT = convert(varchar(254), null), 450 /*CT: IsNullable 0x20 (c.status&8 * 4), 451 MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable 452 IsFixedLength 0x10 - yes 453 IsLong 0x80 - no 454 */ 455 COLUMN_FLAGS = convert(int, c.status & 8) * 12 + 16, 456 usertype = t.usertype, 457 IS_NULLABLE = convert(bit, c.status & 8), 458 DATA_TYPE = convert(smallint, m.data_type), 459 TYPE_GUID = convert(varchar(36), null), 460 CHARACTER_MAXIMUM_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */ 461 CHARACTER_OCTET_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */ 462 /* CT: MONEYN, INTN, DATETIMN and FLOATN will all be included */ 463 NUMERIC_PRECISION = isnull(convert(smallint, c.prec), 464 isnull(convert(smallint, d.data_precision), 465 convert(smallint, c.length))) 466 + isnull(d.aux, convert(smallint, 467 ascii(substring("???AAAFFFCKFOLS", 468 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 469 NUMERIC_PRECISION_RADIX = d.num_prec_radix, 470 TYPE_NAME = 471 case 472 when convert(bit, (c.status & 0x80)) = 1 then 473 rtrim(substring(d.type_name, 474 1 + isnull(d.aux, 475 ascii(substring('III<<<MMMI<<A<A', 476 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 477 1)) - 60), 18)) + ' identity' 478 else 479 rtrim(substring(d.type_name, 480 1 + isnull(d.aux, 481 ascii(substring('III<<<MMMI<<A<A', 482 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 483 1)) - 60), 18)) 484 end, 485 NUMERIC_SCALE = convert(smallint, null), /*CT: only for decimal and numeric in oledb*/ 486 /*CT: in ss_dtype 111, 109, 38, 110, only 111 has non null value for this item 487 it is the datetime type scale, UI4 type*/ 488 DATETIME_PRECISION = case 489 when d.ss_dtype = 111 /*CT: also the type is changed to UI4*/ 490 then 491 isnull(convert(int, c.scale), 492 convert(int, d.numeric_scale)) 493 + convert(int, 494 isnull(d.aux, 495 ascii(substring("<<<<<<<<<<<<<<?", 496 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 497 1)) - 60)) 498 else 499 convert(int, null) 500 end, 501 CHARACTER_SET_CATALOG = null, 502 CHARACTER_SET_SCHEMA = null, 503 CHARACTER_SET_NAME = null, 504 COLLATION_CATALOG = null, 505 COLLATION_SCHEMA = null, 506 COLLATION_NAME = null, 507 DOMAIN_CATALOG = null, 508 DOMAIN_SCHEMA = null, 509 DOMAIN_NAME = null, 510 DESCRIPTION = null, /* Description are NULL */ 511 tds_type = c.type, 512 id = c.id, 513 col_len = c.length, 514 sys_type = t.type 515 516 FROM 517 syscolumns c, 518 sysobjects o, 519 sybsystemprocs.dbo.spt_datatype_info d, 520 systypes t, 521 sybsystemprocs.dbo.spt_sybdrv m 522 WHERE 523 o.name like @table_name 524 AND user_name(o.uid) like @table_owner 525 AND o.id = c.id 526 /* 527 ** We use syscolumn.usertype instead of syscolumn.type 528 ** to do join with systypes.usertype. This is because 529 ** for a column which allows null, type stores its 530 ** Server internal datatype whereas usertype still 531 ** stores its user defintion datatype. For an example, 532 ** a column of type 'decimal NULL', its usertype = 26, 533 ** representing decimal whereas its type = 106 534 ** representing decimaln. nullable in the select list 535 ** already tells user whether the column allows null. 536 ** In the case of user defining datatype, this makes 537 ** more sense for the user. 538 */ 539 AND c.usertype = t.usertype 540 AND t.type = d.ss_dtype 541 AND o.type != 'P' 542 AND c.name like @column_name 543 -- AND d.ss_dtype IN (111, 109, 38, 110) /* Just *N types */ 544 AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */ 545 AND c.usertype < 100 546 AND m.type_name = 547 rtrim(substring(d.type_name, 548 1 + isnull(d.aux, 549 ascii(substring("III<<<MMMI<<A<A", 550 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 551 1)) - 60), 18)) 552 UNION 553 SELECT /* All other types including user data types */ 554 TABLE_CATALOG = DB_NAME(), 555 TABLE_SCHEMA = USER_NAME(o.uid), 556 TABLE_NAME = o.name, 557 COLUMN_NAME = c.name, 558 COLUMN_GUID = convert(varchar(36), null), 559 COLUMN_PROPID = convert(int, null), 560 ORDINAL_POSITION = convert(int, c.colid), 561 COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1), 562 COLUMN_DEFAULT = convert(varchar(254), null), 563 /*CT: IsNullable 0x20 (c.status&8 * 4), 564 MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable 565 IsFixedLength 0x10 - yes 566 IsLong 0x80 - no 567 */ 568 COLUMN_FLAGS = convert(int, c.status & 8) * 12 + 569 case when c.type in (37, 39, 155, 34, 35, 174) then 0 570 when c.type in (47) and (convert(int, c.status & 8) = 1) then 0 571 else 16 end + 572 case when c.type in (34, 35, 174) then 128 /*text, image and unitext type*/ 573 else 0 end, 574 usertype = t.usertype, 575 IS_NULLABLE = convert(bit, c.status & 8), 576 DATA_TYPE = convert(smallint, m.data_type), 577 TYPE_GUID = convert(varchar(36), null), 578 /*CT: only for char, binary and bit in oledb, see spec*/ 579 CHARACTER_MAXIMUM_LENGTH = case 580 when c.type in (135, 155) 581 then 582 (isnull(convert(int, c.prec), 583 isnull(convert(int, d.data_precision), 584 convert(int, c.length))) 585 + isnull(d.aux, convert(int, 586 ascii(substring("???AAAFFFCKFOLS", 587 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))) / 2 588 when c.type in (47, 39, 45, 37, 35, 34, 50) 589 then 590 isnull(convert(int, c.prec), 591 isnull(convert(int, d.data_precision), 592 convert(int, c.length))) 593 + isnull(d.aux, convert(int, 594 ascii(substring("???AAAFFFCKFOLS", 595 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 596 else 597 convert(int, null) 598 end, 599 600 /* 601 ** if the datatype is of type CHAR or BINARY 602 ** then set char_octet_length to the same value 603 ** assigned in the "prec" column. 604 ** 605 ** The first part of the logic is: 606 ** 607 ** if(c.type in (47, 39, 45, 37, 35, 34)) 608 ** set char_octet_length = prec; 609 ** if (c.type in (135, 155) 610 ** set char_octet_length = prec * 2; 611 ** else 612 ** set char_octet_length = null; 613 */ 614 CHARACTER_OCTET_LENGTH = case 615 when c.type in (47, 39, 45, 37, 35, 34, 50) 616 then /*same size as the CHARACTER_MAXIMUM_LENGTH */ 617 isnull(convert(int, c.prec), 618 isnull(convert(int, d.data_precision), 619 convert(int, c.length))) 620 + isnull(d.aux, convert(int, 621 ascii(substring("???AAAFFFCKFOLS", 622 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 623 when c.type in (135, 155) 624 then /* CHARACTER_MAXIMUM_LENGTH * 2 for the unichar, univarchar*/ 625 isnull(convert(int, c.prec), 626 isnull(convert(int, d.data_precision), 627 convert(int, c.length))) 628 + isnull(d.aux, convert(int, 629 ascii(substring("???AAAFFFCKFOLS", 630 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 631 else 632 convert(int, null) 633 end, 634 NUMERIC_PRECISION = case 635 when c.type in (47, 39, 45, 37, 35, 34, 135, 155, 50, 58, 61, 123, 147, 174) 636 then convert(smallint, null) 637 else 638 isnull(convert(smallint, c.prec), 639 isnull(convert(smallint, d.data_precision), 640 convert(smallint, c.length))) 641 + isnull(d.aux, convert(smallint, 642 ascii(substring("???AAAFFFCKFOLS", 643 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)) 644 end, 645 NUMERIC_PRECISION_RADIX = d.numeric_radix, 646 TYPE_NAME = 647 case 648 when convert(bit, (c.status & 0x80)) = 1 then 649 rtrim(substring(d.type_name, 650 1 + isnull(d.aux, 651 ascii(substring('III<<<MMMI<<A<A', 652 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 653 1)) - 60), 18)) + ' identity' 654 else 655 rtrim(substring(d.type_name, 656 1 + isnull(d.aux, 657 ascii(substring('III<<<MMMI<<A<A', 658 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 659 1)) - 60), 18)) 660 end, 661 NUMERIC_SCALE = case 662 when c.type in (63, 108, 55, 106) then 663 isnull(convert(smallint, c.scale), 664 convert(smallint, d.numeric_scale)) + 665 convert(smallint, isnull(d.aux, 666 ascii(substring("<<<<<<<<<<<<<<?", 667 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 668 1)) - 60)) 669 else 670 convert(smallint, null) 671 end, 672 DATETIME_PRECISION = case 673 when c.type in (58, 61, 123, 147, 111) then 674 isnull(convert(int, c.scale), 675 convert(int, d.numeric_scale)) + 676 convert(int, isnull(d.aux, 677 ascii(substring("<<<<<<<<<<<<<<?", 678 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 679 1)) - 60)) 680 else 681 convert(int, null) 682 end, 683 CHARACTER_SET_CATALOG = null, 684 CHARACTER_SET_SCHEMA = null, 685 CHARACTER_SET_NAME = null, 686 COLLATION_CATALOG = null, 687 COLLATION_SCHEMA = null, 688 COLLATION_NAME = null, 689 DOMAIN_CATALOG = null, 690 DOMAIN_SCHEMA = null, 691 DOMAIN_NAME = null, 692 DESCRIPTION = null, 693 tds_type = c.type, 694 id = c.id, 695 col_len = c.length, 696 sys_type = t.type 697 698 FROM 699 syscolumns c, 700 sysobjects o, 701 sybsystemprocs.dbo.spt_datatype_info d, 702 systypes t, 703 sybsystemprocs.dbo.spt_sybdrv m 704 WHERE 705 o.name like @table_name 706 AND user_name(o.uid) like @table_owner 707 AND o.id = c.id 708 /* 709 ** We use syscolumn.usertype instead of syscolumn.type 710 ** to do join with systypes.usertype. This is because 711 ** for a column which allows null, type stores its 712 ** Server internal datatype whereas usertype still 713 ** stores its user defintion datatype. For an example, 714 ** a column of type 'decimal NULL', its usertype = 26, 715 ** representing decimal whereas its type = 106 716 ** representing decimaln. nullable in the select list 717 ** already tells user whether the column allows null. 718 ** In the case of user defining datatype, this makes 719 ** more sense for the user. 720 */ 721 AND c.usertype = t.usertype 722 /* 723 ** We need a equality join with 724 ** sybsystemprocs.dbo.spt_datatype_info here so that 725 ** there is only one qualified row returned from 726 ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding 727 ** duplicates. 728 */ 729 AND t.type = d.ss_dtype 730 AND c.name like @column_name 731 AND o.type != 'P' 732 AND c.name like @column_name 733 -- AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */ 734 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */ 735 736 OR c.usertype >= 100) /* User defined types */ 737 AND m.type_name = 738 rtrim(substring(d.type_name, 739 1 + isnull(d.aux, 740 ascii(substring("III<<<MMMI<<A<A", 741 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 742 1)) - 60), 18)) 743 744 ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 745 end 746 747 SelectClause: 748 749 /* usertype 3 (binary) , 80 (timestamp) */ 750 UPDATE #results_table set COLUMN_FLAGS = 112 where usertype in (3) and IS_NULLABLE = 0 751 UPDATE #results_table set DATA_TYPE = 4 where DATA_TYPE = 5 and tds_type = 59 752 UPDATE #results_table set DATA_TYPE = 4 where DATA_TYPE = 5 and col_len = 4 753 UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 96 and sys_type = 47 and DATA_TYPE = 129 and not (IS_NULLABLE = 1 and @stripblanks = 1) 754 UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 96 and sys_type = 135 and DATA_TYPE = 130 and not (IS_NULLABLE = 1 and @stripblanks = 1) 755 UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 16 and tds_type = 62 and DATA_TYPE = 5 756 UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 16 and tds_type = 59 and DATA_TYPE = 4 757 if (@is_ado = 1) 758 begin 759 select TABLE_CATALOG, 760 TABLE_SCHEMA, 761 TABLE_NAME, 762 COLUMN_NAME, 763 COLUMN_GUID, 764 COLUMN_PROPID, 765 ORDINAL_POSITION = convert(int, row_id), 766 COLUMN_HASDEFAULT, 767 COLUMN_DEFAULT, 768 COLUMN_FLAGS, 769 -- USERTYPE = usertype, 770 IS_NULLABLE, 771 DATA_TYPE, 772 --TYPE_NAME, 773 TYPE_GUID, 774 CHARACTER_MAXIMUM_LENGTH, 775 CHARACTER_OCTET_LENGTH, 776 NUMERIC_PRECISION, 777 -- NUMERIC_PRECISION_RADIX, 778 NUMERIC_SCALE, 779 DATETIME_PRECISION, 780 CHARACTER_SET_CATALOG, 781 CHARACTER_SET_SCHEMA, 782 CHARACTER_SET_NAME, 783 COLLATION_CATALOG, 784 COLLATION_SCHEMA, 785 COLLATION_NAME, 786 DOMAIN_CATALOG, 787 DOMAIN_SCHEMA, 788 DOMAIN_NAME, 789 DESCRIPTION 790 -- TDSTYPE = tds_type, 791 -- COLID = id, 792 -- COLLEN = col_len 793 794 FROM 795 #results_table 796 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 797 798 end 799 else 800 if (@is_ado = 2) 801 begin 802 select TABLE_CATALOG, 803 TABLE_SCHEMA, 804 TABLE_NAME, 805 COLUMN_NAME, 806 COLUMN_GUID, 807 COLUMN_PROPID, 808 ORDINAL_POSITION = convert(int, row_id), 809 COLUMN_HASDEFAULT, 810 COLUMN_DEFAULT, 811 COLUMN_FLAGS, 812 -- USERTYPE = usertype, 813 IS_NULLABLE, 814 --DATA_TYPE , 815 TYPE_NAME, 816 TYPE_GUID, 817 CHARACTER_MAXIMUM_LENGTH, 818 CHARACTER_OCTET_LENGTH, 819 NUMERIC_PRECISION, 820 NUMERIC_PRECISION_RADIX, 821 NUMERIC_SCALE, 822 DATETIME_PRECISION, 823 CHARACTER_SET_CATALOG, 824 CHARACTER_SET_SCHEMA, 825 CHARACTER_SET_NAME, 826 COLLATION_CATALOG, 827 COLLATION_SCHEMA, 828 COLLATION_NAME, 829 DOMAIN_CATALOG, 830 DOMAIN_SCHEMA, 831 DOMAIN_NAME, 832 DESCRIPTION 833 -- TDSTYPE = tds_type, 834 -- COLID = id, 835 -- COLLEN = col_len 836 837 FROM 838 #results_table 839 order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION 840 end 841 842 843 if (@startedInTransaction = 1) 844 rollback transaction oledb_keep_temptable_tx 845 846 return (0) 847
exec sp_procxmode 'sp_oledb_columns', 'AnyMode' go Grant Execute on sp_oledb_columns to public go
| RESULT SETS | |
| sp_oledb_columns_rset_002 | |
| sp_oledb_columns_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysobjects reads table sybsystemprocs..syscolumns reads table sybsystemprocs..systypes read_writes table tempdb..#results_table (1) reads table sybsystemprocs..spt_datatype_info reads table sybsystemprocs..spt_sybdrv |
