| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_jdbc_columns | | 31 Aug 14 | Defects Dependencies |
1 /** SECTION END: CLEANUP **/ 2 3 4 /* This is the version for servers which support UNION */ 5 6 CREATE PROCEDURE sp_jdbc_columns( 7 @table_name varchar(771), 8 @table_owner varchar(32) = null, 9 @table_qualifier varchar(32) = null, 10 @column_name varchar(771) = null, 11 @version int = null /* Conform to JDBC 4.0 spec if @version is not null */ 12 ) 13 AS 14 /* Don't delete the following line. It is the checkpoint for sed */ 15 /* Server dependent stored procedure add here ad ADDPOINT_COLS */ 16 17 declare @o_uid int 18 declare @o_name varchar(257) 19 declare @d_data_type smallint 20 declare @d_aux int 21 declare @d_ss_dtype tinyint 22 declare @d_type_name varchar(257) 23 declare @d_data_precision int 24 declare @d_numeric_scale smallint 25 declare @d_numeric_radix smallint 26 declare @d_sql_data_type smallint 27 declare @c_name varchar(257) 28 declare @c_length int 29 declare @c_prec tinyint 30 declare @c_scale tinyint 31 declare @c_type tinyint 32 declare @c_colid smallint 33 declare @c_status tinyint 34 declare @c_cdefault int 35 declare @xtname varchar(255) 36 declare @ident bit 37 38 declare @msg varchar(250) 39 declare @full_table_name varchar(1542) 40 declare @table_id int 41 declare @char_bin_types varchar(30) 42 declare @uni_types varchar(10) 43 declare @column_default varchar(1024) 44 declare @startedInTransaction bit 45 46 47 48 49 50 if (@@trancount = 0) 51 begin 52 set chained off 53 end 54 55 if (@@trancount > 0) 56 select @startedInTransaction = 1 57 else 58 select @startedInTransaction = 0 59 60 set transaction isolation level 1 61 62 if (@startedInTransaction = 1) 63 save transaction jdbc_keep_temptables_from_tx 64 65 /* this will make sure that all rows are sent even if 66 ** the client "set rowcount" is differect 67 */ 68 69 set rowcount 0 70 71 /* character and binary datatypes */ 72 select @char_bin_types = 73 char(47) + char(39) + char(45) + char(37) + char(35) + char(34) 74 75 /* unichar, univarchar and unitext datatypes */ 76 /* Note that the actual type numbers are 155 (unichar), 135 (univarchar) 77 and 174 (unitext), but because of issues that can arise when a server 78 has utf-8 as the default charset and a non-binary sort order, we need 79 to create a character string that is valid in utf-8. Therefore we 80 apply an offset of 60 to move the characters to be valid utf-8 chars. 81 The stored proc later does a similar calculation to utilize these 82 values properly*/ 83 select @uni_types = 84 char(95) + char(75) + char(114) 85 86 if @column_name is null select @column_name = '%' 87 88 if @table_qualifier is not null 89 begin 90 if db_name() != @table_qualifier 91 begin /* 92 ** If qualifier doesn't match current database: 18039 93 ** Table qualifier must be name of current database 94 */ 95 exec sp_getmessage 18039, @msg output 96 raiserror 18039 @msg 97 return (1) 98 end 99 end 100 101 if @table_name is null 102 begin /* If table name not supplied, match all */ 103 select @table_name = '%' 104 end 105 106 if @table_owner is null 107 begin /* If unqualified table name */ 108 SELECT @full_table_name = @table_name 109 select @table_owner = '%' 110 end 111 else 112 begin /* Qualified table name */ 113 SELECT @full_table_name = @table_owner + '.' + @table_name 114 end 115 116 /* create the temp table to hold our results */ 117 118 create table #jdbc_columns( 119 TABLE_CAT varchar(32) null, 120 TABLE_SCHEM varchar(32) null, 121 TABLE_NAME varchar(257) null, 122 COLUMN_NAME varchar(257) null, 123 DATA_TYPE smallint null, 124 TYPE_NAME varchar(255) null, 125 COLUMN_SIZE int null, 126 BUFFER_LENGTH int null, 127 DECIMAL_DIGITS int null, 128 NUM_PREC_RADIX int null, 129 NULLABLE int null, 130 REMARKS varchar(255) null, 131 COLUMN_DEF varchar(512) null, 132 SQL_DATA_TYPE int null, 133 SQL_DATETIME_SUB int null, 134 CHAR_OCTET_LENGTH int null, 135 ORDINAL_POSITION int null, 136 IS_NULLABLE varchar(10) null, 137 SCOPE_CATLOG varchar(32) null, 138 SCOPE_SCHEMA varchar(32) null, 139 SCOPE_TABLE varchar(32) null, 140 SOURCE_DATA_TYPE smallint null, 141 IS_AUTOINCREMENT varchar(10) null) 142 143 144 145 /* Decide if we're going to take the branch where we are getting 146 information on one table (first branch), or more than one*/ 147 148 /* Get Object ID */ 149 SELECT @table_id = object_id(@full_table_name) 150 /* If the table name parameter is valid, get the information */ 151 if ((charindex('%', @full_table_name) = 0) and 152 (charindex('_', @full_table_name) = 0) and 153 (@table_id != 0)) 154 155 begin 156 157 declare jdbc_columns_cursor1 cursor for 158 SELECT 159 c.cdefault, 160 c.colid, 161 c.length, 162 c.name, 163 c.prec, 164 c.scale, 165 c.status, 166 c.type, 167 d.aux, 168 d.data_precision, 169 d.data_type, 170 d.numeric_radix, 171 d.numeric_scale, 172 d.sql_data_type, 173 d.ss_dtype, 174 t.name, 175 o.name, 176 o.uid, 177 xtname, 178 convert(bit, (c.status & 0x80)) 179 180 FROM 181 syscolumns c, 182 sysobjects o, 183 sybsystemprocs.dbo.spt_jdbc_datatype_info d, 184 sysxtypes x, 185 systypes t 186 WHERE 187 o.id = @table_id 188 AND o.id = c.id 189 /* 190 ** We use syscolumn.usertype instead of syscolumn.type 191 ** to do join with systypes.usertype. This is because 192 ** for a column which allows null, type stores its 193 ** Server internal datatype whereas usertype still 194 ** stores its user defintion datatype. For an example, 195 ** a column of type 'decimal NULL', its usertype = 26, 196 ** representing decimal whereas its type = 106 197 ** representing decimaln. nullable in the select list 198 ** already tells user whether the column allows null. 199 ** In the case of user defining datatype, this makes 200 ** more sense for the user. 201 */ 202 AND c.usertype = t.usertype 203 AND t.type = d.ss_dtype 204 and c.xtype *= x.xtid 205 AND o.type != 'P' 206 AND c.name like @column_name ESCAPE '\' 207 AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */ 208 AND c.usertype < 100 209 210 open jdbc_columns_cursor1 211 212 fetch jdbc_columns_cursor1 into 213 @c_cdefault, 214 @c_colid, 215 @c_length, 216 @c_name, 217 @c_prec, 218 @c_scale, 219 @c_status, 220 @c_type, 221 @d_aux, 222 @d_data_precision, 223 @d_data_type, 224 @d_numeric_radix, 225 @d_numeric_scale, 226 @d_sql_data_type, 227 @d_ss_dtype, 228 @d_type_name, 229 @o_name, 230 @o_uid, 231 @xtname, 232 @ident 233 234 /* INTn, FLOATn, DATETIMEn and MONEYn types */ 235 236 while (@@sqlstatus = 0) 237 begin 238 239 exec sp_drv_column_default @c_cdefault, @column_default out 240 241 INSERT INTO #jdbc_columns values ( 242 /* TABLE_CAT */ 243 DB_NAME(), 244 245 /* TABLE_SCHEM */ 246 USER_NAME(@o_uid), 247 248 /* TABLE_NAME */ 249 @o_name, 250 251 /* COLUMN_NAME */ 252 @c_name, 253 254 /* DATA_TYPE */ 255 @d_data_type + convert(smallint, 256 isnull(@d_aux, 257 ascii(substring('666AAA@@@CB??GG', 258 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 259 - 60)), 260 261 /* TYPE_NAME */ 262 case 263 when @ident = 1 then 264 case 265 when @d_type_name = 'usmallint' then 'unsigned smallint identity' 266 when @d_type_name = 'uint' then 'unsigned int identity' 267 when @d_type_name = 'ubigint' then 'unsigned bigint identity' 268 else 269 isnull(@xtname, rtrim(substring(@d_type_name, 270 1 + isnull(@d_aux, 271 ascii(substring('III<<<MMMI<<A<A', 272 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 273 1)) - 60), 255))) + ' identity' 274 end 275 when @d_type_name = 'usmallint' then 'unsigned smallint' 276 when @d_type_name = 'uint' then 'unsigned int' 277 when @d_type_name = 'ubigint' then 'unsigned bigint' 278 else 279 isnull(@xtname, rtrim(substring(@d_type_name, 280 1 + isnull(@d_aux, 281 ascii(substring('III<<<MMMI<<A<A', 282 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 283 1)) - 60), 255))) 284 end, 285 286 /* COLUMN_SIZE */ 287 isnull(convert(int, @c_prec), 288 isnull(convert(int, @d_data_precision), 289 convert(int, @c_length))) 290 + isnull(@d_aux, convert(int, 291 ascii(substring('???AAAFFFCKFOLS', 292 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 293 294 /* BUFFER_LENGTH */ 295 isnull(convert(int, @c_prec), 296 isnull(convert(int, @d_data_precision), 297 convert(int, @c_length))) 298 + isnull(@d_aux, convert(int, 299 ascii(substring('???AAAFFFCKFOLS', 300 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 301 302 /* DECIMAL_DIGITS */ 303 isnull(convert(smallint, @c_scale), 304 convert(smallint, @d_numeric_scale)) + 305 convert(smallint, isnull(@d_aux, 306 ascii(substring('<<<<<<<<<<<<<<?', 307 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 308 1)) - 60)), 309 310 /* NUM_PREC_RADIX */ 311 @d_numeric_radix, 312 313 /* NULLABLE */ 314 /* set nullability from status flag */ 315 convert(smallint, convert(bit, @c_status & 8)), 316 317 /* REMARKS */ 318 convert(varchar(254), null), /* Remarks are NULL */ 319 320 /* COLUMN_DEF */ 321 @column_default, 322 323 /* SQL_DATA_TYPE */ 324 isnull(@d_sql_data_type, 325 @d_data_type + convert(smallint, 326 isnull(@d_aux, 327 ascii(substring('666AAA@@@CB??GG', 328 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 329 - 60))), 330 331 /* SQL_DATATIME_SUB */ 332 NULL, 333 334 /* CHAR_OCTET_LENGTH */ 335 /* 336 ** if the datatype is of type CHAR or BINARY 337 ** then set char_octet_length to the same value 338 ** assigned in the "prec" column. 339 ** 340 ** The first part of the logic is: 341 ** 342 ** if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34)) 343 ** set char_octet_length = prec; 344 ** else 345 ** set char_octet_length = 0; 346 */ 347 /* 348 ** check if in the list 349 ** if so, return a 1 and multiply it by the precision 350 ** if not, return a 0 and multiply it by the precision 351 */ 352 convert(smallint, 353 substring('0111111', 354 charindex(char(@c_type), @char_bin_types) + 355 charindex(char(@c_type - 60), @uni_types) + 1, 1)) * 356 /* calculate the precision */ 357 isnull(convert(int, @c_prec), 358 isnull(convert(int, @d_data_precision), 359 convert(int, @c_length))) 360 + isnull(@d_aux, convert(int, 361 ascii(substring('???AAAFFFCKFOLS', 362 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 363 364 /* ORDINAL_POSITION */ 365 @c_colid, 366 367 /* IS_NULLABLE */ 368 rtrim(substring('NO YES', convert(smallint, 369 convert(bit, @c_status & 8) * 3) + 1, 3)), 370 null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 371 REF data type not supported*/ 372 /* IS_AUTOINCREMENT */ 373 rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3)) 374 375 ) /* close paren for values (*) */ 376 377 fetch jdbc_columns_cursor1 into 378 @c_cdefault, 379 @c_colid, 380 @c_length, 381 @c_name, 382 @c_prec, 383 @c_scale, 384 @c_status, 385 @c_type, 386 @d_aux, 387 @d_data_precision, 388 @d_data_type, 389 @d_numeric_radix, 390 @d_numeric_scale, 391 @d_sql_data_type, 392 @d_ss_dtype, 393 @d_type_name, 394 @o_name, 395 @o_uid, 396 @xtname, 397 @ident 398 399 end 400 401 deallocate cursor jdbc_columns_cursor1 402 403 declare jdbc_columns_cursor2 cursor for 404 SELECT 405 c.cdefault, 406 c.colid, 407 c.length, 408 c.name, 409 c.prec, 410 c.scale, 411 c.status, 412 c.type, 413 d.aux, 414 d.data_precision, 415 d.data_type, 416 d.numeric_radix, 417 d.numeric_scale, 418 d.sql_data_type, 419 d.ss_dtype, 420 t.name, 421 o.name, 422 o.uid, 423 xtname, 424 convert(bit, (c.status & 0x80)) 425 FROM 426 syscolumns c, 427 sysobjects o, 428 sybsystemprocs.dbo.spt_jdbc_datatype_info d, 429 sysxtypes x, 430 systypes t 431 WHERE 432 o.id = @table_id 433 AND o.id = c.id 434 /* 435 ** We use syscolumn.usertype instead of syscolumn.type 436 ** to do join with systypes.usertype. This is because 437 ** for a column which allows null, type stores its 438 ** Server internal datatype whereas usertype still 439 ** stores its user defintion datatype. For an example, 440 ** a column of type 'decimal NULL', its usertype = 26, 441 ** representing decimal whereas its type = 106 442 ** representing decimaln. nullable in the select list 443 ** already tells user whether the column allows null. 444 ** In the case of user defining datatype, this makes 445 ** more sense for the user. 446 */ 447 AND c.usertype = t.usertype 448 /* 449 ** We need a equality join with 450 ** sybsystemprocs.dbo.spt_jdbc_datatype_info here so that 451 ** there is only one qualified row returned from 452 ** sybsystemprocs.dbo.spt_jdbc_datatype_info, thus avoiding 453 ** duplicates. 454 */ 455 AND t.type = d.ss_dtype 456 and c.xtype *= x.xtid 457 AND o.type != 'P' 458 AND c.name like @column_name ESCAPE '\' 459 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */ 460 OR c.usertype >= 100) /* User defined types */ 461 462 open jdbc_columns_cursor2 463 464 fetch jdbc_columns_cursor2 into 465 @c_cdefault, 466 @c_colid, 467 @c_length, 468 @c_name, 469 @c_prec, 470 @c_scale, 471 @c_status, 472 @c_type, 473 @d_aux, 474 @d_data_precision, 475 @d_data_type, 476 @d_numeric_radix, 477 @d_numeric_scale, 478 @d_sql_data_type, 479 @d_ss_dtype, 480 @d_type_name, 481 @o_name, 482 @o_uid, 483 @xtname, 484 @ident 485 486 while (@@sqlstatus = 0) 487 begin 488 489 exec sp_drv_column_default @c_cdefault, @column_default out 490 491 /* All other types including user data types */ 492 493 INSERT INTO #jdbc_columns values ( 494 495 /* TABLE_CAT */ 496 DB_NAME(), 497 498 /* TABLE_SCHEM */ 499 USER_NAME(@o_uid), 500 501 /* TABLE_NAME */ 502 @o_name, 503 504 /*COLUMN_NAME*/ 505 @c_name, 506 507 /* DATA_TYPE */ 508 @d_data_type + convert(smallint, 509 isnull(@d_aux, 510 ascii(substring('666AAA@@@CB??GG', 511 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 512 - 60)), 513 514 /* TYPE_NAME */ 515 case 516 when @ident = 1 then 517 case 518 when @d_type_name = 'usmallint' then 'unsigned smallint identity' 519 when @d_type_name = 'uint' then 'unsigned int identity' 520 when @d_type_name = 'ubigint' then 'unsigned bigint identity' 521 else 522 isnull(@xtname, rtrim(substring(@d_type_name, 523 1 + isnull(@d_aux, 524 ascii(substring('III<<<MMMI<<A<A', 525 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 526 1)) - 60), 255))) + ' identity' 527 end 528 when @d_type_name = 'usmallint' then 'unsigned smallint' 529 when @d_type_name = 'uint' then 'unsigned int' 530 when @d_type_name = 'ubigint' then 'unsigned bigint' 531 else 532 isnull(@xtname, rtrim(substring(@d_type_name, 533 1 + isnull(@d_aux, 534 ascii(substring('III<<<MMMI<<A<A', 535 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 536 1)) - 60), 255))) 537 end, 538 539 /* COLUMN_SIZE */ 540 isnull(convert(int, @c_prec), 541 isnull(convert(int, @d_data_precision), 542 convert(int, @c_length / (1 + 543 (convert(smallint, 544 substring('011', 545 charindex(char(@c_type - 60), @uni_types) + 1, 1))))))) 546 + isnull(@d_aux, convert(int, 547 ascii(substring('???AAAFFFCKFOLS', 548 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 549 550 /* BUFFER_LENGTH */ 551 isnull(convert(int, @c_prec), 552 isnull(convert(int, @d_data_precision), 553 convert(int, @c_length))) 554 + isnull(@d_aux, convert(int, 555 ascii(substring('???AAAFFFCKFOLS', 556 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 557 558 /* DECIMAL_DIGITS */ 559 isnull(convert(smallint, @c_scale), 560 convert(smallint, @d_numeric_scale)) + 561 convert(smallint, isnull(@d_aux, 562 ascii(substring('<<<<<<<<<<<<<<?', 563 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 564 1)) - 60)), 565 566 /* NUM_PREC_RADIX */ 567 @d_numeric_radix, 568 569 /* NULLABLE */ 570 convert(smallint, convert(bit, @c_status & 8)), 571 572 /* REMARKS */ 573 convert(varchar(254), null), 574 575 /* COLUMN_DEF */ 576 @column_default, 577 578 /* SQL_DATA_TYPE */ 579 isnull(@d_sql_data_type, 580 @d_data_type + convert(smallint, 581 isnull(@d_aux, 582 ascii(substring('666AAA@@@CB??GG', 583 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 584 - 60))), 585 586 /* SQL_DATETIME_SUB */ 587 NULL, 588 589 /* CHAR_OCTET_LENGTH */ 590 /* 591 ** if the datatype is of type CHAR or BINARY 592 ** then set char_octet_length to the same value 593 ** assigned in the "prec" column. 594 ** 595 ** The first part of the logic is: 596 ** 597 ** if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34)) 598 ** set char_octet_length = prec; 599 ** else 600 ** set char_octet_length = 0; 601 */ 602 603 /* 604 ** check if in the list 605 ** if so, return a 1 and multiply it by the precision 606 ** if not, return a 0 and multiply it by the precision 607 */ 608 convert(smallint, substring('0111111', 609 charindex(char(@c_type), @char_bin_types) + 610 charindex(char(@c_type - 60), @uni_types) + 1, 1)) * 611 /* calculate the precision */ 612 isnull(convert(int, @c_prec), 613 isnull(convert(int, @d_data_precision), 614 convert(int, @c_length))) 615 + isnull(@d_aux, convert(int, 616 ascii(substring('???AAAFFFCKFOLS', 617 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 618 619 /* ORDINAL_POSITION */ 620 @c_colid, 621 622 /* IS_NULLABLE */ 623 rtrim(substring('NO YES', convert(smallint, 624 convert(bit, @c_status & 8) * 3) + 1, 3)), 625 null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 626 REF data type not supported*/ 627 /* IS_AUTOINCREMENT */ 628 rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3)) 629 630 631 ) /* close paren for values (*) */ 632 633 fetch jdbc_columns_cursor2 into 634 @c_cdefault, 635 @c_colid, 636 @c_length, 637 @c_name, 638 @c_prec, 639 @c_scale, 640 @c_status, 641 @c_type, 642 @d_aux, 643 @d_data_precision, 644 @d_data_type, 645 @d_numeric_radix, 646 @d_numeric_scale, 647 @d_sql_data_type, 648 @d_ss_dtype, 649 @d_type_name, 650 @o_name, 651 @o_uid, 652 @xtname, 653 @ident 654 655 end /* while loop */ 656 657 deallocate cursor jdbc_columns_cursor2 658 659 end /* if we have just one table */ 660 661 else 662 begin 663 664 /* We'll be iterating over more than one table */ 665 666 declare jdbc_columns_cursor3 cursor for 667 select 668 c.cdefault, 669 c.colid, 670 c.length, 671 c.name, 672 c.prec, 673 c.scale, 674 c.status, 675 c.type, 676 d.aux, 677 d.data_precision, 678 d.data_type, 679 d.numeric_radix, 680 d.numeric_scale, 681 d.sql_data_type, 682 d.ss_dtype, 683 t.name, 684 o.name, 685 o.uid, 686 xtname, 687 convert(bit, (c.status & 0x80)) 688 689 FROM 690 syscolumns c, 691 sysobjects o, 692 sybsystemprocs.dbo.spt_jdbc_datatype_info d, 693 sysxtypes x, 694 systypes t 695 696 WHERE 697 o.name like @table_name ESCAPE '\' 698 AND user_name(o.uid) like @table_owner ESCAPE '\' 699 AND o.id = c.id 700 /* 701 ** We use syscolumn.usertype instead of syscolumn.type 702 ** to do join with systypes.usertype. This is because 703 ** for a column which allows null, type stores its 704 ** Server internal datatype whereas usertype still 705 ** stores its user defintion datatype. For an example, 706 ** a column of type 'decimal NULL', its usertype = 26, 707 ** representing decimal whereas its type = 106 708 ** representing decimaln. nullable in the select list 709 ** already tells user whether the column allows null. 710 ** In the case of user defining datatype, this makes 711 ** more sense for the user. 712 */ 713 AND c.usertype = t.usertype 714 AND t.type = d.ss_dtype 715 AND o.type != 'P' 716 and c.xtype *= x.xtid 717 AND c.name like @column_name ESCAPE '\' 718 AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */ 719 AND c.usertype < 100 720 721 open jdbc_columns_cursor3 722 723 fetch jdbc_columns_cursor3 into 724 @c_cdefault, 725 @c_colid, 726 @c_length, 727 @c_name, 728 @c_prec, 729 @c_scale, 730 @c_status, 731 @c_type, 732 @d_aux, 733 @d_data_precision, 734 @d_data_type, 735 @d_numeric_radix, 736 @d_numeric_scale, 737 @d_sql_data_type, 738 @d_ss_dtype, 739 @d_type_name, 740 @o_name, 741 @o_uid, 742 @xtname, 743 @ident 744 745 746 /* INTn, FLOATn, DATETIMEn and MONEYn types */ 747 748 while (@@sqlstatus = 0) 749 begin 750 751 exec sp_drv_column_default @c_cdefault, @column_default out 752 753 INSERT INTO #jdbc_columns values ( 754 /* TABLE_CAT */ 755 DB_NAME(), 756 757 /* TABLE_SCHEM */ 758 USER_NAME(@o_uid), 759 760 /* TABLE_NAME */ 761 @o_name, 762 763 /* COLUMN_NAME */ 764 @c_name, 765 766 /* DATA_TYPE */ 767 @d_data_type + convert(smallint, 768 isnull(@d_aux, 769 ascii(substring('666AAA@@@CB??GG', 770 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 771 - 60)), 772 773 /* TYPE_NAME */ 774 case 775 when @ident = 1 then 776 case 777 when @d_type_name = 'usmallint' then 'unsigned smallint identity' 778 when @d_type_name = 'uint' then 'unsigned int identity' 779 when @d_type_name = 'ubigint' then 'unsigned bigint identity' 780 else 781 isnull(@xtname, rtrim(substring(@d_type_name, 782 1 + isnull(@d_aux, 783 ascii(substring('III<<<MMMI<<A<A', 784 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 785 1)) - 60), 255))) + ' identity' 786 end 787 when @d_type_name = 'usmallint' then 'unsigned smallint' 788 when @d_type_name = 'uint' then 'unsigned int' 789 when @d_type_name = 'ubigint' then 'unsigned bigint' 790 else 791 isnull(@xtname, rtrim(substring(@d_type_name, 792 1 + isnull(@d_aux, 793 ascii(substring('III<<<MMMI<<A<A', 794 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 795 1)) - 60), 255))) 796 end, 797 798 /* COLUMN_SIZE */ 799 isnull(convert(int, @c_prec), 800 isnull(convert(int, @d_data_precision), 801 convert(int, @c_length))) 802 + isnull(@d_aux, convert(int, 803 ascii(substring('???AAAFFFCKFOLS', 804 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 805 806 /* BUFFER_LENGTH */ 807 isnull(convert(int, @c_prec), 808 isnull(convert(int, @d_data_precision), 809 convert(int, @c_length))) 810 + isnull(@d_aux, convert(int, 811 ascii(substring('???AAAFFFCKFOLS', 812 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 813 814 /* DECIMAL_DIGITS */ 815 isnull(convert(smallint, @c_scale), 816 convert(smallint, @d_numeric_scale)) + 817 convert(smallint, isnull(@d_aux, 818 ascii(substring('<<<<<<<<<<<<<<?', 819 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 820 1)) - 60)), 821 822 /* NUM_PREC_RADIX */ 823 @d_numeric_radix, 824 825 /* NULLABLE */ 826 /* set nullability from status flag */ 827 convert(smallint, convert(bit, @c_status & 8)), 828 829 /* REMARKS */ 830 convert(varchar(254), null), /* Remarks are NULL */ 831 832 /* COLUMN_DEF */ 833 @column_default, 834 835 /* SQL_DATA_TYPE */ 836 isnull(@d_sql_data_type, 837 @d_data_type + convert(smallint, 838 isnull(@d_aux, 839 ascii(substring('666AAA@@@CB??GG', 840 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 841 - 60))), 842 843 /* SQL_DATATIME_SUB */ 844 NULL, 845 846 /* CHAR_OCTET_LENGTH */ 847 /* 848 ** if the datatype is of type CHAR or BINARY 849 ** then set char_octet_length to the same value 850 ** assigned in the "prec" column. 851 ** 852 ** The first part of the logic is: 853 ** 854 ** if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34)) 855 ** set char_octet_length = prec; 856 ** else 857 ** set char_octet_length = 0; 858 */ 859 860 /* 861 ** check if in the list 862 ** if so, return a 1 and multiply it by the precision 863 ** if not, return a 0 and multiply it by the precision 864 */ 865 convert(smallint, 866 substring('0111111', 867 charindex(char(@c_type), @char_bin_types) + 868 charindex(char(@c_type - 60), @uni_types) + 1, 1)) * 869 /* calculate the precision */ 870 isnull(convert(int, @c_prec), 871 isnull(convert(int, @d_data_precision), 872 convert(int, @c_length))) 873 + isnull(@d_aux, convert(int, 874 ascii(substring('???AAAFFFCKFOLS', 875 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 876 877 /* ORDINAL_POSITION */ 878 @c_colid, 879 880 /* IS_NULLABLE */ 881 rtrim(substring('NO YES', convert(smallint, 882 convert(bit, @c_status & 8) * 3) + 1, 3)), 883 null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 884 REF data type not supported*/ 885 /* IS_AUTOINCREMENT */ 886 rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3)) 887 888 ) /* right paren for values (*) */ 889 890 fetch jdbc_columns_cursor3 into 891 @c_cdefault, 892 @c_colid, 893 @c_length, 894 @c_name, 895 @c_prec, 896 @c_scale, 897 @c_status, 898 @c_type, 899 @d_aux, 900 @d_data_precision, 901 @d_data_type, 902 @d_numeric_radix, 903 @d_numeric_scale, 904 @d_sql_data_type, 905 @d_ss_dtype, 906 @d_type_name, 907 @o_name, 908 @o_uid, 909 @xtname, 910 @ident 911 912 end /* while loop */ 913 914 deallocate cursor jdbc_columns_cursor3 915 916 declare jdbc_columns_cursor4 cursor for 917 SELECT 918 c.cdefault, 919 c.colid, 920 c.length, 921 c.name, 922 c.prec, 923 c.scale, 924 c.status, 925 c.type, 926 d.aux, 927 d.data_precision, 928 d.data_type, 929 d.numeric_radix, 930 d.numeric_scale, 931 d.sql_data_type, 932 d.ss_dtype, 933 t.name, 934 o.name, 935 o.uid, 936 xtname, 937 convert(bit, (c.status & 0x80)) 938 FROM 939 syscolumns c, 940 sysobjects o, 941 sybsystemprocs.dbo.spt_jdbc_datatype_info d, 942 sysxtypes x, 943 systypes t 944 WHERE 945 o.name like @table_name ESCAPE '\' 946 AND user_name(o.uid) like @table_owner ESCAPE '\' 947 and c.xtype *= x.xtid 948 AND o.id = c.id 949 /* 950 ** We use syscolumn.usertype instead of syscolumn.type 951 ** to do join with systypes.usertype. This is because 952 ** for a column which allows null, type stores its 953 ** Server internal datatype whereas usertype still 954 ** stores its user defintion datatype. For an example, 955 ** a column of type 'decimal NULL', its usertype = 26, 956 ** representing decimal whereas its type = 106 957 ** representing decimaln. nullable in the select list 958 ** already tells user whether the column allows null. 959 ** In the case of user defining datatype, this makes 960 ** more sense for the user. 961 */ 962 AND c.usertype = t.usertype 963 /* 964 ** We need a equality join with 965 ** sybsystemprocs.dbo.spt_jdbc_datatype_info here so that 966 ** there is only one qualified row returned from 967 ** sybsystemprocs.dbo.spt_jdbc_datatype_info, thus avoiding 968 ** duplicates. 969 */ 970 AND t.type = d.ss_dtype 971 AND o.type != 'P' 972 AND c.name like @column_name ESCAPE '\' 973 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */ 974 OR c.usertype >= 100) /* User defined types */ 975 976 open jdbc_columns_cursor4 977 978 fetch jdbc_columns_cursor4 into 979 @c_cdefault, 980 @c_colid, 981 @c_length, 982 @c_name, 983 @c_prec, 984 @c_scale, 985 @c_status, 986 @c_type, 987 @d_aux, 988 @d_data_precision, 989 @d_data_type, 990 @d_numeric_radix, 991 @d_numeric_scale, 992 @d_sql_data_type, 993 @d_ss_dtype, 994 @d_type_name, 995 @o_name, 996 @o_uid, 997 @xtname, 998 @ident 999 1000 1001 while (@@sqlstatus = 0) 1002 begin 1003 exec sp_drv_column_default @c_cdefault, @column_default out 1004 1005 /* All other types including user data types */ 1006 INSERT INTO #jdbc_columns values ( 1007 1008 /* TABLE_CAT */ 1009 DB_NAME(), 1010 1011 /* TABLE_SCHEM */ 1012 USER_NAME(@o_uid), 1013 1014 /* TABLE_NAME */ 1015 @o_name, 1016 1017 /*COLUMN_NAME*/ 1018 @c_name, 1019 1020 /* DATA_TYPE */ 1021 @d_data_type + convert(smallint, 1022 isnull(@d_aux, 1023 ascii(substring('666AAA@@@CB??GG', 1024 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 1025 - 60)), 1026 1027 /* TYPE_NAME */ 1028 case 1029 when @ident = 1 then 1030 case 1031 when @d_type_name = 'usmallint' then 'unsigned smallint identity' 1032 when @d_type_name = 'uint' then 'unsigned int identity' 1033 when @d_type_name = 'ubigint' then 'unsigned bigint identity' 1034 else 1035 isnull(@xtname, rtrim(substring(@d_type_name, 1036 1 + isnull(@d_aux, 1037 ascii(substring('III<<<MMMI<<A<A', 1038 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1039 1)) - 60), 255))) + ' identity' 1040 end 1041 when @d_type_name = 'usmallint' then 'unsigned smallint' 1042 when @d_type_name = 'uint' then 'unsigned int' 1043 when @d_type_name = 'ubigint' then 'unsigned bigint' 1044 else 1045 isnull(@xtname, rtrim(substring(@d_type_name, 1046 1 + isnull(@d_aux, 1047 ascii(substring('III<<<MMMI<<A<A', 1048 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1049 1)) - 60), 255))) 1050 end, 1051 1052 /* COLUMN_SIZE */ 1053 isnull(convert(int, @c_prec), 1054 isnull(convert(int, @d_data_precision), 1055 convert(int, @c_length / (1 + 1056 (convert(smallint, 1057 substring('011', 1058 charindex(char(@c_type - 60), @uni_types) + 1, 1))))))) 1059 + isnull(@d_aux, convert(int, 1060 ascii(substring('???AAAFFFCKFOLS', 1061 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 1062 1063 /* BUFFER_LENGTH */ 1064 isnull(convert(int, @c_prec), 1065 isnull(convert(int, @d_data_precision), 1066 convert(int, @c_length))) 1067 + isnull(@d_aux, convert(int, 1068 ascii(substring('???AAAFFFCKFOLS', 1069 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 1070 1071 /* DECIMAL_DIGITS */ 1072 isnull(convert(smallint, @c_scale), 1073 convert(smallint, @d_numeric_scale)) + 1074 convert(smallint, isnull(@d_aux, 1075 ascii(substring('<<<<<<<<<<<<<<?', 1076 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1077 1)) - 60)), 1078 1079 /* NUM_PREC_RADIX */ 1080 @d_numeric_radix, 1081 1082 /* NULLABLE */ 1083 convert(smallint, convert(bit, @c_status & 8)), 1084 1085 /* REMARKS */ 1086 convert(varchar(254), null), 1087 1088 /* COLUMN_DEF */ 1089 @column_default, 1090 1091 /* SQL_DATA_TYPE */ 1092 isnull(@d_sql_data_type, 1093 @d_data_type + convert(smallint, 1094 isnull(@d_aux, 1095 ascii(substring('666AAA@@@CB??GG', 1096 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) 1097 - 60))), 1098 1099 /* SQL_DATETIME_SUB */ 1100 NULL, 1101 1102 /* CHAR_OCTET_LENGTH */ 1103 /* 1104 ** if the datatype is of type CHAR or BINARY 1105 ** then set char_octet_length to the same value 1106 ** assigned in the "prec" column. 1107 ** 1108 ** The first part of the logic is: 1109 ** 1110 ** if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34)) 1111 ** set char_octet_length = prec; 1112 ** else 1113 ** set char_octet_length = 0; 1114 */ 1115 1116 /* 1117 ** check if in the list 1118 ** if so, return a 1 and multiply it by the precision 1119 ** if not, return a 0 and multiply it by the precision 1120 */ 1121 convert(smallint, substring('0111111', 1122 charindex(char(@c_type), @char_bin_types) + 1123 charindex(char(@c_type - 60), @uni_types) + 1, 1)) * 1124 /* calculate the precision */ 1125 isnull(convert(int, @c_prec), 1126 isnull(convert(int, @d_data_precision), 1127 convert(int, @c_length))) 1128 + isnull(@d_aux, convert(int, 1129 ascii(substring('???AAAFFFCKFOLS', 1130 2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)), 1131 1132 /* ORDINAL_POSITION */ 1133 @c_colid, 1134 1135 /* IS_NULLABLE */ 1136 rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 8) * 3) + 1, 3)), 1137 null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 1138 REF data type not supported*/ 1139 /* IS_AUTOINCREMENT */ 1140 rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3)) 1141 1142 ) /* close paren for values (*) */ 1143 1144 fetch jdbc_columns_cursor4 into 1145 @c_cdefault, 1146 @c_colid, 1147 @c_length, 1148 @c_name, 1149 @c_prec, 1150 @c_scale, 1151 @c_status, 1152 @c_type, 1153 @d_aux, 1154 @d_data_precision, 1155 @d_data_type, 1156 @d_numeric_radix, 1157 @d_numeric_scale, 1158 @d_sql_data_type, 1159 @d_ss_dtype, 1160 @d_type_name, 1161 @o_name, 1162 @o_uid, 1163 @xtname, 1164 @ident 1165 1166 end /* while loop */ 1167 1168 deallocate cursor jdbc_columns_cursor4 1169 1170 end 1171 if @version is not null 1172 begin 1173 /* Adaptive Server has expanded all '*' elements in the following statement */ SELECT #jdbc_columns.TABLE_CAT, #jdbc_columns.TABLE_SCHEM, #jdbc_columns.TABLE_NAME, #jdbc_columns.COLUMN_NAME, #jdbc_columns.DATA_TYPE, #jdbc_columns.TYPE_NAME, #jdbc_columns.COLUMN_SIZE, #jdbc_columns.BUFFER_LENGTH, #jdbc_columns.DECIMAL_DIGITS, #jdbc_columns.NUM_PREC_RADIX, #jdbc_columns.NULLABLE, #jdbc_columns.REMARKS, #jdbc_columns.COLUMN_DEF, #jdbc_columns.SQL_DATA_TYPE, #jdbc_columns.SQL_DATETIME_SUB, #jdbc_columns.CHAR_OCTET_LENGTH, #jdbc_columns.ORDINAL_POSITION, #jdbc_columns.IS_NULLABLE, #jdbc_columns.SCOPE_CATLOG, #jdbc_columns.SCOPE_SCHEMA, #jdbc_columns.SCOPE_TABLE, #jdbc_columns.SOURCE_DATA_TYPE, #jdbc_columns.IS_AUTOINCREMENT FROM #jdbc_columns 1174 ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION 1175 end 1176 else 1177 SELECT TABLE_CAT 1178 , TABLE_SCHEM 1179 , TABLE_NAME 1180 , COLUMN_NAME 1181 , DATA_TYPE 1182 , TYPE_NAME 1183 , COLUMN_SIZE 1184 , BUFFER_LENGTH 1185 , DECIMAL_DIGITS 1186 , NUM_PREC_RADIX 1187 , NULLABLE 1188 , REMARKS 1189 , COLUMN_DEF 1190 , SQL_DATA_TYPE 1191 , SQL_DATETIME_SUB 1192 , CHAR_OCTET_LENGTH 1193 , ORDINAL_POSITION 1194 , IS_NULLABLE 1195 , SCOPE_CATLOG 1196 , SCOPE_SCHEMA 1197 , SCOPE_TABLE 1198 , SOURCE_DATA_TYPE 1199 FROM #jdbc_columns 1200 ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION 1201 1202 drop table #jdbc_columns 1203 1204 if (@startedInTransaction = 1) 1205 rollback transaction jdbc_keep_temptables_from_tx 1206 1207 return (0) 1208 1209 1210
exec sp_procxmode 'sp_jdbc_columns', 'AnyMode' go Grant Execute on sp_jdbc_columns to public go
| RESULT SETS | |
| sp_jdbc_columns_rset_002 | |
| sp_jdbc_columns_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..systypes read_writes table tempdb..#jdbc_columns (1) calls proc sybsystemprocs..sp_getmessage  reads table master..syslanguages (1)  reads table sybsystemprocs..sysusermessages calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table master..sysmessages (1) calls proc sybsystemprocs..sp_drv_column_default reads table sybsystemprocs..syscomments reads table sybsystemprocs..sysxtypes reads table sybsystemprocs..syscolumns reads table sybsystemprocs..spt_jdbc_datatype_info reads table sybsystemprocs..sysobjects |
