Database | Proc | Application | Created | Links |
sybsystemprocs | sp_odbc_columns ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G% " */ 3 /* 10.0 07/20/93 sproc/columns */ 4 5 6 /* This is the version for servers which support UNION */ 7 8 /* This routine is intended for support of ODBC connectivity. Under no 9 ** circumstances should changes be made to this routine unless they are 10 ** to fix ODBC related problems. All other users are at there own risk! 11 ** 12 ** Please be aware that any changes made to this file (or any other ODBC 13 ** support routine) will require Sybase to recertify the SQL server as 14 ** ODBC compliant. This process is currently being managed internally 15 ** by the "Interoperability Engineering Technology Solutions Group" here 16 ** within Sybase. 17 */ 18 19 CREATE PROCEDURE sp_odbc_columns( 20 @table_name varchar(771), 21 @table_owner varchar(32) = null, 22 @table_qualifier varchar(32) = null, 23 @column_name varchar(771) = null) 24 AS 25 declare @full_table_name varchar(1543) 26 declare @table_id int 27 declare @char_bin_types varchar(32) 28 29 declare @o_uid int 30 declare @o_name varchar(255) 31 declare @d_data_type smallint 32 declare @d_aux int 33 declare @d_ss_dtype tinyint 34 declare @d_type_name varchar(255) 35 declare @d_data_precision int 36 declare @d_numeric_scale smallint 37 declare @d_numeric_radix smallint 38 declare @d_sql_data_type smallint 39 declare @c_name varchar(255) 40 declare @c_length int 41 declare @c_prec tinyint 42 declare @c_scale tinyint 43 declare @c_type tinyint 44 declare @c_colid smallint 45 declare @c_status tinyint 46 declare @c_cdefault int 47 declare @xtname varchar(255) 48 declare @column_default varchar(1024) 49 declare @ident bit 50 declare @max_cdefault_len int 51 declare @cdefault_len int 52 53 declare @startedInTransaction bit 54 if (@@trancount > 0) 55 select @startedInTransaction = 1 56 else 57 select @startedInTransaction = 0 58 59 60 61 set transaction isolation level 1 62 63 if (@startedInTransaction = 1) 64 save transaction odbc_keep_temptable_tx 65 66 /* character and binary datatypes */ 67 select @char_bin_types = 68 char(47) + char(39) + char(45) + char(37) + char(35) + char(34) 69 70 if @column_name is null /* If column name not supplied, match all */ 71 select @column_name = '%' 72 73 /* Check if the current database is the same as the one provided */ 74 if @table_qualifier is not null 75 begin 76 if db_name() != @table_qualifier 77 begin /* 78 ** If qualifier doesn't match current database 79 ** 18039, Table qualifier must be name of current database 80 */ 81 raiserror 18039 82 return (1) 83 end 84 end 85 86 if @table_name is null 87 begin /* If table name not supplied, match all */ 88 select @table_name = '%' 89 end 90 91 if @table_owner is null 92 begin /* If unqualified table name */ 93 SELECT @full_table_name = @table_name 94 end 95 else 96 begin /* Qualified table name */ 97 SELECT @full_table_name = @table_owner + '.' + @table_name 98 end 99 100 /* Get Object ID */ 101 SELECT @table_id = object_id(@full_table_name) 102 103 104 /* create the temp table to hold column results */ 105 create table #odbc_columns( 106 TABLE_CAT varchar(32) null, 107 TABLE_SCHEM varchar(32) null, 108 TABLE_NAME varchar(255) null, 109 COLUMN_NAME varchar(255) null, 110 DATA_TYPE smallint null, 111 TYPE_NAME varchar(255) null, 112 COLUMN_SIZE int null, 113 BUFFER_LENGTH int null, 114 DECIMAL_DIGITS smallint null, 115 NUM_PREC_RADIX smallint null, 116 NULLABLE smallint null, 117 REMARKS varchar(255) null, 118 COLUMN_DEF varchar(512) null, 119 SQL_DATA_TYPE smallint null, 120 SQL_DATETIME_SUB smallint null, 121 CHAR_OCTET_LENGTH int null, 122 ORDINAL_POSITION int null, 123 IS_NULLABLE varchar(10) null) 124 125 select @max_cdefault_len = 512 126 127 /* If the table name parameter is valid, get the information */ 128 if ((charindex('%', @full_table_name) = 0) and 129 (charindex('_', @full_table_name) = 0) and 130 @table_id != 0) 131 begin 132 declare odbc_columns_cursor1 cursor for 133 SELECT 134 c.cdefault, 135 c.colid, 136 c.length, 137 c.name, 138 c.prec, 139 c.scale, 140 c.status, 141 c.type, 142 d.aux, 143 d.data_precision, 144 d.data_type, 145 d.numeric_radix, 146 d.numeric_scale, 147 d.sql_data_type, 148 d.ss_dtype, 149 case 150 when c.usertype = 80 then t.name 151 when c.usertype = 24 then t.name 152 when c.usertype = 25 then t.name 153 else 154 d.type_name 155 end, 156 157 o.name, 158 o.uid, 159 xtname, 160 convert(bit, (c.status & 0x80)) 161 FROM 162 syscolumns c, 163 sysobjects o, 164 sybsystemprocs.dbo.spt_datatype_info d, 165 sysxtypes x, 166 systypes t 167 WHERE 168 o.id = @table_id 169 AND c.id = o.id 170 /* 171 ** We use syscolumn.usertype instead of syscolumn.type 172 ** to do join with systypes.usertype. This is because 173 ** for a column which allows null, type stores its 174 ** Server internal datatype whereas usertype still 175 ** stores its user defintion datatype. For an example, 176 ** a column of type 'decimal NULL', its usertype = 26, 177 ** representing decimal whereas its type = 106 178 ** representing decimaln. nullable in the select list 179 ** already tells user whether the column allows null. 180 ** In the case of user defining datatype, this makes 181 ** more sense for the user. 182 */ 183 AND c.usertype = t.usertype 184 AND t.type = d.ss_dtype 185 AND c.xtype *= x.xtid 186 AND o.type != 'P' 187 AND c.name like @column_name 188 -- AND d.ss_dtype IN (111, 109, 38, 110) /* Just *N types */ 189 AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */ 190 AND c.usertype < 100 /* No user defined types */ 191 192 open odbc_columns_cursor1 193 194 fetch odbc_columns_cursor1 into 195 @c_cdefault, 196 @c_colid, 197 @c_length, 198 @c_name, 199 @c_prec, 200 @c_scale, 201 @c_status, 202 @c_type, 203 @d_aux, 204 @d_data_precision, 205 @d_data_type, 206 @d_numeric_radix, 207 @d_numeric_scale, 208 @d_sql_data_type, 209 @d_ss_dtype, 210 @d_type_name, 211 @o_name, 212 @o_uid, 213 @xtname, 214 @ident 215 216 while (@@sqlstatus = 0) 217 begin 218 if (@c_cdefault is NOT NULL) 219 begin 220 exec sp_drv_column_default @c_cdefault, @column_default out 221 select @cdefault_len = datalength(@column_default) 222 223 if (@cdefault_len > @max_cdefault_len) 224 select @column_default = "TRUNCATED" 225 226 /* INTn, FLOATn, DATETIMEn and MONEYn types */ 227 INSERT INTO #odbc_columns values ( 228 DB_NAME(), 229 USER_NAME(@o_uid), 230 @o_name, 231 @c_name, 232 @d_data_type + convert(smallint, 233 isnull(@d_aux, 234 ascii(substring("666AAA@@@CB??GG", 235 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 236 - 60)), 237 /* TYPE_NAME */ 238 case 239 when @ident = 1 then 240 rtrim(substring(@d_type_name, 241 1 + isnull(@d_aux, 242 ascii(substring("III<<<MMMI<<A<A", 243 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 244 1)) - 60), 18)) + ' identity' 245 else 246 rtrim(substring(@d_type_name, 247 1 + isnull(@d_aux, 248 ascii(substring("III<<<MMMI<<A<A", 249 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 250 1)) - 60), 18)) 251 end, 252 isnull(convert(int, @c_prec), 253 isnull(convert(int, @d_data_precision), 254 convert(int, @c_length))) 255 + isnull(@d_aux, convert(int, 256 ascii(substring("???AAAFFFCKFOLS", 257 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 258 isnull(convert(int, @c_length), 259 convert(int, @c_length)) + 260 convert(int, isnull(@d_aux, 261 ascii(substring("AAA<BB<DDDHJSPP", 262 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 263 1)) - 64)), 264 isnull(convert(smallint, @c_scale), 265 convert(smallint, @d_numeric_scale)) 266 + convert(smallint, 267 isnull(@d_aux, 268 ascii(substring("<<<<<<<<<<<<<<?", 269 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 270 1)) - 60)), 271 @d_numeric_radix, 272 /* set nullability from status flag */ 273 convert(smallint, convert(bit, @c_status & 8)), 274 convert(varchar(254), null), /* Remarks are NULL */ 275 @column_default, 276 isnull(@d_sql_data_type, 277 @d_data_type + convert(smallint, 278 isnull(@d_aux, 279 ascii(substring("666AAA@@@CB??GG", 280 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 281 - 60))), 282 convert(smallint, NULL), 283 /* 284 ** if the datatype is of type CHAR or BINARY 285 ** then set char_octet_length to the same value 286 ** assigned in the "prec" column. 287 ** 288 ** The first part of the logic is: 289 ** 290 ** if(@c_type is in (47, 39, 45, 37, 35, 34)) 291 ** set char_octet_length = prec; 292 ** else 293 ** set char_octet_length = 0; 294 */ 295 /* 296 ** check if in the list 297 ** if so, return a 1 and multiply it by the precision 298 ** if not, return a 0 and multiply it by the precision 299 */ 300 convert(smallint, 301 substring('0111111', 302 charindex(char(@c_type), 303 @char_bin_types) + 1, 1)) * 304 /* calculate the precision */ 305 isnull(convert(int, @c_prec), 306 isnull(convert(int, @d_data_precision), 307 convert(int, @c_length))) 308 + isnull(@d_aux, convert(int, 309 ascii(substring('???AAAFFFCKFOLS', 310 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 311 convert(int, @c_colid), 312 rtrim(substring('NO YES', 313 (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3))) 314 315 fetch odbc_columns_cursor1 into 316 @c_cdefault, 317 @c_colid, 318 @c_length, 319 @c_name, 320 @c_prec, 321 @c_scale, 322 @c_status, 323 @c_type, 324 @d_aux, 325 @d_data_precision, 326 @d_data_type, 327 @d_numeric_radix, 328 @d_numeric_scale, 329 @d_sql_data_type, 330 @d_ss_dtype, 331 @d_type_name, 332 @o_name, 333 @o_uid, 334 @xtname, 335 @ident 336 337 end /* end of if */ 338 end /* end of while */ 339 340 deallocate cursor odbc_columns_cursor1 341 declare odbc_columns_cursor2 cursor for 342 SELECT 343 c.cdefault, 344 c.colid, 345 c.length, 346 c.name, 347 c.prec, 348 c.scale, 349 c.status, 350 c.type, 351 d.aux, 352 d.data_precision, 353 d.data_type, 354 d.numeric_radix, 355 d.numeric_scale, 356 d.sql_data_type, 357 d.ss_dtype, 358 case 359 when c.usertype = 80 then t.name 360 when c.usertype = 24 then t.name 361 when c.usertype = 25 then t.name 362 else 363 d.type_name 364 end, 365 o.name, 366 o.uid, 367 xtname, 368 convert(bit, (c.status & 0x80)) 369 FROM 370 syscolumns c, 371 sysobjects o, 372 sybsystemprocs.dbo.spt_datatype_info d, 373 sysxtypes x, 374 systypes t 375 WHERE 376 o.id = @table_id 377 AND c.id = o.id 378 /* 379 ** We use syscolumn.usertype instead of syscolumn.type 380 ** to do join with systypes.usertype. This is because 381 ** for a column which allows null, type stores its 382 ** Server internal datatype whereas usertype still 383 ** stores its user defintion datatype. For an example, 384 ** a column of type 'decimal NULL', its usertype = 26, 385 ** representing decimal whereas its type = 106 386 ** representing decimaln. nullable in the select list 387 ** already tells user whether the column allows null. 388 ** In the case of user defining datatype, this makes 389 ** more sense for the user. 390 */ 391 AND c.usertype = t.usertype 392 /* 393 ** We need a equality join with 394 ** sybsystemprocs.dbo.spt_datatype_info here so that 395 ** there is only one qualified row returned from 396 ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding 397 ** duplicates. 398 */ 399 AND t.type = d.ss_dtype 400 AND c.xtype *= x.xtid 401 AND o.type != 'P' 402 AND c.name like @column_name 403 -- AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */ 404 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */ 405 OR c.usertype >= 100) /* User defined types */ 406 407 open odbc_columns_cursor2 408 fetch odbc_columns_cursor2 into 409 @c_cdefault, 410 @c_colid, 411 @c_length, 412 @c_name, 413 @c_prec, 414 @c_scale, 415 @c_status, 416 @c_type, 417 @d_aux, 418 @d_data_precision, 419 @d_data_type, 420 @d_numeric_radix, 421 @d_numeric_scale, 422 @d_sql_data_type, 423 @d_ss_dtype, 424 @d_type_name, 425 @o_name, 426 @o_uid, 427 @xtname, 428 @ident 429 430 while (@@sqlstatus = 0) 431 begin 432 if (@c_cdefault is NOT NULL) 433 begin 434 exec sp_drv_column_default @c_cdefault, @column_default out 435 select @cdefault_len = datalength(@column_default) 436 437 if (@cdefault_len > @max_cdefault_len) 438 select @column_default = "TRUNCATED" 439 440 /* All other types including user data types */ 441 INSERT INTO #odbc_columns values ( 442 DB_NAME(), 443 USER_NAME(@o_uid), 444 @o_name, 445 @c_name, 446 @d_data_type + convert(smallint, 447 isnull(@d_aux, 448 ascii(substring("666AAA@@@CB??GG", 449 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 450 - 60)), 451 /* TYPE_NAME */ 452 case 453 when @ident = 1 then 454 rtrim(substring(@d_type_name, 455 1 + isnull(@d_aux, 456 ascii(substring("III<<<MMMI<<A<A", 457 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 458 1)) - 60), 18)) + ' identity' 459 else 460 rtrim(substring(@d_type_name, 461 1 + isnull(@d_aux, 462 ascii(substring("III<<<MMMI<<A<A", 463 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 464 1)) - 60), 18)) 465 end, 466 isnull(convert(int, @c_prec), 467 isnull(convert(int, @d_data_precision), 468 convert(int, @c_length))) 469 + isnull(@d_aux, convert(int, 470 ascii(substring("???AAAFFFCKFOLS", 471 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 472 isnull(convert(int, @c_length), 473 convert(int, @c_length)) + 474 convert(int, isnull(@d_aux, 475 ascii(substring("AAA<BB<DDDHJSPP", 476 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 477 1)) - 64)), 478 isnull(convert(smallint, @c_scale), 479 convert(smallint, @d_numeric_scale)) + 480 convert(smallint, isnull(@d_aux, 481 ascii(substring("<<<<<<<<<<<<<<?", 482 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 483 1)) - 60)), 484 @d_numeric_radix, 485 /* set nullability from status flag */ 486 convert(smallint, convert(bit, @c_status & 8)), 487 convert(varchar(254), null), /* Remarks are NULL */ 488 @column_default, 489 isnull(@d_sql_data_type, 490 @d_data_type + convert(smallint, 491 isnull(@d_aux, 492 ascii(substring("666AAA@@@CB??GG", 493 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 494 - 60))), 495 convert(smallint, NULL), 496 /* 497 ** if the datatype is of type CHAR or BINARY 498 ** then set char_octet_length to the same value 499 ** assigned in the "prec" column. 500 ** 501 ** The first part of the logic is: 502 ** 503 ** if(@c_type is in (47, 39, 45, 37, 35, 34)) 504 ** set char_octet_length = prec; 505 ** else 506 ** set char_octet_length = 0; 507 */ 508 /* 509 ** check if in the list 510 ** if so, return a 1 and multiply it by the precision 511 ** if not, return a 0 and multiply it by the precision 512 */ 513 convert(smallint, 514 substring('0111111', 515 charindex(char(@c_type), 516 @char_bin_types) + 1, 1)) * 517 /* calculate the precision */ 518 isnull(convert(int, @c_prec), 519 isnull(convert(int, @d_data_precision), 520 convert(int, @c_length))) 521 + isnull(@d_aux, convert(int, 522 ascii(substring('???AAAFFFCKFOLS', 523 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 524 convert(int, @c_colid), 525 rtrim(substring('NO YES', 526 (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3))) 527 528 fetch odbc_columns_cursor2 into 529 @c_cdefault, 530 @c_colid, 531 @c_length, 532 @c_name, 533 @c_prec, 534 @c_scale, 535 @c_status, 536 @c_type, 537 @d_aux, 538 @d_data_precision, 539 @d_data_type, 540 @d_numeric_radix, 541 @d_numeric_scale, 542 @d_sql_data_type, 543 @d_ss_dtype, 544 @d_type_name, 545 @o_name, 546 @o_uid, 547 @xtname, 548 @ident 549 550 end /* end of if */ 551 552 end /* while loop */ 553 deallocate cursor odbc_columns_cursor2 554 end 555 else 556 begin 557 /* 558 ** This block is for the case where there IS pattern 559 ** matching done on the table name. 560 */ 561 if @table_owner is null /* If owner not supplied, match all */ 562 select @table_owner = '%' 563 564 declare odbc_columns_cursor3 cursor for 565 select 566 c.cdefault, 567 c.colid, 568 c.length, 569 c.name, 570 c.prec, 571 c.scale, 572 c.status, 573 c.type, 574 d.aux, 575 d.data_precision, 576 d.data_type, 577 d.numeric_radix, 578 d.numeric_scale, 579 d.sql_data_type, 580 d.ss_dtype, 581 case 582 when c.usertype = 80 then t.name 583 when c.usertype = 24 then t.name 584 when c.usertype = 25 then t.name 585 else 586 d.type_name 587 end, 588 o.name, 589 o.uid, 590 xtname, 591 convert(bit, (c.status & 0x80)) 592 FROM 593 syscolumns c, 594 sysobjects o, 595 sybsystemprocs.dbo.spt_datatype_info d, 596 sysxtypes x, 597 systypes t 598 WHERE 599 o.name like @table_name 600 AND user_name(o.uid) like @table_owner 601 AND o.id = c.id 602 /* 603 ** We use syscolumn.usertype instead of syscolumn.type 604 ** to do join with systypes.usertype. This is because 605 ** for a column which allows null, type stores its 606 ** Server internal datatype whereas usertype still 607 ** stores its user defintion datatype. For an example, 608 ** a column of type 'decimal NULL', its usertype = 26, 609 ** representing decimal whereas its type = 106 610 ** representing decimaln. nullable in the select list 611 ** already tells user whether the column allows null. 612 ** In the case of user defining datatype, this makes 613 ** more sense for the user. 614 */ 615 AND c.usertype = t.usertype 616 AND t.type = d.ss_dtype 617 AND o.type != 'P' 618 AND c.xtype *= x.xtid 619 AND c.name like @column_name 620 -- AND d.ss_dtype IN (111, 109, 38, 110) /* Just *N types */ 621 AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */ 622 AND c.usertype < 100 623 624 open odbc_columns_cursor3 625 626 fetch odbc_columns_cursor3 into 627 @c_cdefault, 628 @c_colid, 629 @c_length, 630 @c_name, 631 @c_prec, 632 @c_scale, 633 @c_status, 634 @c_type, 635 @d_aux, 636 @d_data_precision, 637 @d_data_type, 638 @d_numeric_radix, 639 @d_numeric_scale, 640 @d_sql_data_type, 641 @d_ss_dtype, 642 @d_type_name, 643 @o_name, 644 @o_uid, 645 @xtname, 646 @ident 647 648 649 /* INTn, FLOATn, DATETIMEn and MONEYn types */ 650 651 while (@@sqlstatus = 0) 652 begin 653 if (@c_cdefault is NOT NULL) 654 begin 655 exec sp_drv_column_default @c_cdefault, @column_default out 656 select @cdefault_len = datalength(@column_default) 657 658 if (@cdefault_len > @max_cdefault_len) 659 select @column_default = "TRUNCATED" 660 661 /* INTn, FLOATn, DATETIMEn and MONEYn types */ 662 INSERT INTO #odbc_columns values ( 663 DB_NAME(), 664 USER_NAME(@o_uid), 665 @o_name, 666 @c_name, 667 @d_data_type + convert(smallint, 668 isnull(@d_aux, 669 ascii(substring("666AAA@@@CB??GG", 670 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 671 - 60)), 672 /* TYPE_NAME */ 673 case 674 when @ident = 1 then 675 rtrim(substring(@d_type_name, 676 1 + isnull(@d_aux, 677 ascii(substring("III<<<MMMI<<A<A", 678 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 679 1)) - 60), 18)) + ' identity' 680 else 681 rtrim(substring(@d_type_name, 682 1 + isnull(@d_aux, 683 ascii(substring("III<<<MMMI<<A<A", 684 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 685 1)) - 60), 18)) 686 end, 687 isnull(convert(int, @c_prec), 688 isnull(convert(int, @d_data_precision), 689 convert(int, @c_length))) 690 + isnull(@d_aux, convert(int, 691 ascii(substring("???AAAFFFCKFOLS", 692 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 693 isnull(convert(int, @c_length), 694 convert(int, @c_length)) + 695 convert(int, isnull(@d_aux, 696 ascii(substring("AAA<BB<DDDHJSPP", 697 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 698 1)) - 64)), 699 isnull(convert(smallint, @c_scale), 700 convert(smallint, @d_numeric_scale)) + 701 convert(smallint, isnull(@d_aux, 702 ascii(substring("<<<<<<<<<<<<<<?", 703 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 704 1)) - 60)), 705 @d_numeric_radix, 706 /* set nullability from status flag */ 707 convert(smallint, convert(bit, @c_status & 8)), 708 convert(varchar(254), null), /* Remarks are NULL */ 709 @column_default, 710 isnull(@d_sql_data_type, 711 @d_data_type + convert(smallint, 712 isnull(@d_aux, 713 ascii(substring("666AAA@@@CB??GG", 714 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 715 - 60))), 716 convert(smallint, NULL), 717 /* 718 ** if the datatype is of type CHAR or BINARY 719 ** then set char_octet_length to the same value 720 ** assigned in the "prec" column. 721 ** 722 ** The first part of the logic is: 723 ** 724 ** if(@c_type is in (47, 39, 45, 37, 35, 34)) 725 ** set char_octet_length = prec; 726 ** else 727 ** set char_octet_length = 0; 728 */ 729 /* 730 ** check if in the list 731 ** if so, return a 1 and multiply it by the precision 732 ** if not, return a 0 and multiply it by the precision 733 */ 734 convert(smallint, 735 substring('0111111', 736 charindex(char(@c_type), 737 @char_bin_types) + 1, 1)) * 738 /* calculate the precision */ 739 isnull(convert(int, @c_prec), 740 isnull(convert(int, @d_data_precision), 741 convert(int, @c_length))) 742 + isnull(@d_aux, convert(int, 743 ascii(substring('???AAAFFFCKFOLS', 744 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 745 convert(int, @c_colid), 746 rtrim(substring('NO YES', 747 (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3))) 748 fetch odbc_columns_cursor3 into 749 @c_cdefault, 750 @c_colid, 751 @c_length, 752 @c_name, 753 @c_prec, 754 @c_scale, 755 @c_status, 756 @c_type, 757 @d_aux, 758 @d_data_precision, 759 @d_data_type, 760 @d_numeric_radix, 761 @d_numeric_scale, 762 @d_sql_data_type, 763 @d_ss_dtype, 764 @d_type_name, 765 @o_name, 766 @o_uid, 767 @xtname, 768 @ident 769 end /* end of if */ 770 771 end /* while loop */ 772 773 deallocate cursor odbc_columns_cursor3 774 declare odbc_columns_cursor4 cursor for 775 SELECT 776 c.cdefault, 777 c.colid, 778 c.length, 779 c.name, 780 c.prec, 781 c.scale, 782 c.status, 783 c.type, 784 d.aux, 785 d.data_precision, 786 d.data_type, 787 d.numeric_radix, 788 d.numeric_scale, 789 d.sql_data_type, 790 d.ss_dtype, 791 case 792 when c.usertype = 80 then t.name 793 when c.usertype = 24 then t.name 794 when c.usertype = 25 then t.name 795 else 796 d.type_name 797 end, 798 o.name, 799 o.uid, 800 xtname, 801 convert(bit, (c.status & 0x80)) 802 FROM 803 syscolumns c, 804 sysobjects o, 805 sybsystemprocs.dbo.spt_datatype_info d, 806 sysxtypes x, 807 systypes t 808 WHERE 809 o.name like @table_name 810 AND user_name(o.uid) like @table_owner 811 AND o.id = c.id 812 /* 813 ** We use syscolumn.usertype instead of syscolumn.type 814 ** to do join with systypes.usertype. This is because 815 ** for a column which allows null, type stores its 816 ** Server internal datatype whereas usertype still 817 ** stores its user defintion datatype. For an example, 818 ** a column of type 'decimal NULL', its usertype = 26, 819 ** representing decimal whereas its type = 106 820 ** representing decimaln. nullable in the select list 821 ** already tells user whether the column allows null. 822 ** In the case of user defining datatype, this makes 823 ** more sense for the user. 824 */ 825 AND c.usertype = t.usertype 826 /* 827 ** We need a equality join with 828 ** sybsystemprocs.dbo.spt_datatype_info here so that 829 ** there is only one qualified row returned from 830 ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding 831 ** duplicates. 832 */ 833 AND t.type = d.ss_dtype 834 AND c.name like @column_name 835 AND o.type != 'P' 836 AND c.xtype *= x.xtid 837 AND c.name like @column_name 838 -- AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */ 839 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */ 840 OR c.usertype >= 100) /* User defined types */ 841 842 open odbc_columns_cursor4 843 844 fetch odbc_columns_cursor4 into 845 @c_cdefault, 846 @c_colid, 847 @c_length, 848 @c_name, 849 @c_prec, 850 @c_scale, 851 @c_status, 852 @c_type, 853 @d_aux, 854 @d_data_precision, 855 @d_data_type, 856 @d_numeric_radix, 857 @d_numeric_scale, 858 @d_sql_data_type, 859 @d_ss_dtype, 860 @d_type_name, 861 @o_name, 862 @o_uid, 863 @xtname, 864 @ident 865 866 867 while (@@sqlstatus = 0) 868 begin 869 if (@c_cdefault is NOT NULL) 870 begin 871 exec sp_drv_column_default @c_cdefault, @column_default out 872 select @cdefault_len = datalength(@column_default) 873 874 if (@cdefault_len > @max_cdefault_len) 875 select @column_default = "TRUNCATED" 876 877 /* All other types including user data types */ 878 INSERT INTO #odbc_columns values ( 879 /* TABLE_CAT */ 880 DB_NAME(), 881 /* TABLE_SCHEM */ 882 USER_NAME(@o_uid), 883 /* TABLE_NAME */ 884 @o_name, 885 /*COLUMN_NAME*/ 886 @c_name, 887 /* DATA_TYPE */ 888 @d_data_type + convert(smallint, 889 isnull(@d_aux, 890 ascii(substring("666AAA@@@CB??GG", 891 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 892 - 60)), 893 /* TYPE_NAME */ 894 case 895 when @ident = 1 then 896 rtrim(substring(@d_type_name, 897 1 + isnull(@d_aux, 898 ascii(substring("III<<<MMMI<<A<A", 899 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 900 1)) - 60), 18)) + ' identity' 901 else 902 rtrim(substring(@d_type_name, 903 1 + isnull(@d_aux, 904 ascii(substring("III<<<MMMI<<A<A", 905 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 906 1)) - 60), 18)) 907 end, 908 /* COLUMN_SIZE */ 909 isnull(convert(int, @c_prec), 910 isnull(convert(int, @d_data_precision), 911 convert(int, @c_length))) 912 + isnull(@d_aux, convert(int, 913 ascii(substring("???AAAFFFCKFOLS", 914 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 915 /* BUFFER_LENGTH */ 916 isnull(convert(int, @c_length), 917 convert(int, @c_length)) + 918 convert(int, isnull(@d_aux, 919 ascii(substring("AAA<BB<DDDHJSPP", 920 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 921 1)) - 64)), 922 /* DECIMAL_DIGITS */ 923 isnull(convert(smallint, @c_scale), 924 convert(smallint, @d_numeric_scale)) + 925 convert(smallint, isnull(@d_aux, 926 ascii(substring("<<<<<<<<<<<<<<?", 927 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 928 1)) - 60)), 929 /* NUM_PREC_RADIX */ 930 @d_numeric_radix, 931 /* NULLABLE */ 932 /* set nullability from status flag */ 933 convert(smallint, convert(bit, @c_status & 8)), 934 /* REMARKS */ 935 convert(varchar(254), null), 936 /* COLUMN_DEF */ 937 @column_default, 938 /* SQL_DATA_TYPE */ 939 isnull(@d_sql_data_type, 940 @d_data_type + convert(smallint, 941 isnull(@d_aux, 942 ascii(substring("666AAA@@@CB??GG", 943 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 944 - 60))), 945 /* SQL_DATETIME_SUB */ 946 convert(smallint, NULL), 947 /* 948 ** if the datatype is of type CHAR or BINARY 949 ** then set char_octet_length to the same value 950 ** assigned in the "prec" column. 951 ** 952 ** The first part of the logic is: 953 ** 954 ** if(@c_type is in (47, 39, 45, 37, 35, 34)) 955 ** set char_octet_length = prec; 956 ** else 957 ** set char_octet_length = 0; 958 */ 959 /* 960 ** check if in the list 961 ** if so, return a 1 and multiply it by the precision 962 ** if not, return a 0 and multiply it by the precision 963 */ 964 /* CHAR_OCTET_LENGTH */ 965 convert(smallint, 966 substring('0111111', 967 charindex(char(@c_type), 968 @char_bin_types) + 1, 1)) * 969 /* calculate the precision */ 970 isnull(convert(int, @c_prec), 971 isnull(convert(int, @d_data_precision), 972 convert(int, @c_length))) 973 + isnull(@d_aux, convert(int, 974 ascii(substring('???AAAFFFCKFOLS', 975 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 976 /* ORDINAL_POSITION */ 977 convert(int, @c_colid), 978 /* IS_NULLABLE */ 979 rtrim(substring('NO YES', 980 (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3))) 981 fetch odbc_columns_cursor4 into 982 @c_cdefault, 983 @c_colid, 984 @c_length, 985 @c_name, 986 @c_prec, 987 @c_scale, 988 @c_status, 989 @c_type, 990 @d_aux, 991 @d_data_precision, 992 @d_data_type, 993 @d_numeric_radix, 994 @d_numeric_scale, 995 @d_sql_data_type, 996 @d_ss_dtype, 997 @d_type_name, 998 @o_name, 999 @o_uid, 1000 @xtname, 1001 @ident 1002 end /* end of if */ 1003 1004 end /* while loop */ 1005 1006 deallocate cursor odbc_columns_cursor4 1007 end /* Adaptive Server has expanded all '*' elements in the following statement */ 1008 SELECT #odbc_columns.TABLE_CAT, #odbc_columns.TABLE_SCHEM, #odbc_columns.TABLE_NAME, #odbc_columns.COLUMN_NAME, #odbc_columns.DATA_TYPE, #odbc_columns.TYPE_NAME, #odbc_columns.COLUMN_SIZE, #odbc_columns.BUFFER_LENGTH, #odbc_columns.DECIMAL_DIGITS, #odbc_columns.NUM_PREC_RADIX, #odbc_columns.NULLABLE, #odbc_columns.REMARKS, #odbc_columns.COLUMN_DEF, #odbc_columns.SQL_DATA_TYPE, #odbc_columns.SQL_DATETIME_SUB, #odbc_columns.CHAR_OCTET_LENGTH, #odbc_columns.ORDINAL_POSITION, #odbc_columns.IS_NULLABLE FROM #odbc_columns ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION 1009 drop table #odbc_columns 1010 if (@startedInTransaction = 1) 1011 rollback transaction odbc_keep_temptable_tx 1012 1013 return (0) 1014
exec sp_procxmode 'sp_odbc_columns', 'AnyMode' go Grant Execute on sp_odbc_columns to public go
RESULT SETS | |
sp_odbc_columns_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED reads table sybsystemprocs..syscolumns ![]() reads table sybsystemprocs..spt_datatype_info ![]() calls proc sybsystemprocs..sp_drv_column_default ![]() reads table sybsystemprocs..syscomments ![]() reads table sybsystemprocs..sysxtypes ![]() reads table sybsystemprocs..systypes ![]() reads table sybsystemprocs..sysobjects ![]() read_writes table tempdb..#odbc_columns (1) |