Database | Proc | Application | Created | Links |
sybsystemprocs | sp_oledb_getprocedurecolumns ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 3 /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */ 4 5 /* 6 ** Messages for "sp_oledb_getprocedurecolumns" 7 ** 8 ** 18039, "Table qualifier must be name of current database" 9 */ 10 11 create procedure sp_oledb_getprocedurecolumns 12 @procedure_name varchar(771) = '%', /* name of stored procedure */ 13 @procedure_schema varchar(32) = null, /* owner of stored procedure */ 14 @procedure_catalog varchar(32) = null, /* name of current database */ 15 @parameter_name varchar(771) = null, /* col name or param name */ 16 @is_ado int = 1 17 as 18 19 declare @msg varchar(255) 20 declare @group_num int 21 declare @semi_position int 22 declare @full_procedure_name varchar(1543) 23 declare @procedure_id int 24 declare @char_bin_types varchar(30) 25 declare @sptlang int 26 declare @startedInTransaction bit 27 if (@@trancount > 0) 28 select @startedInTransaction = 1 29 else 30 select @startedInTransaction = 0 31 32 if @@trancount = 0 33 begin 34 set chained off 35 end 36 37 set transaction isolation level 1 38 39 if (@startedInTransaction = 1) 40 save transaction oledb_keep_temptable_tx 41 42 select @sptlang = @@langid 43 44 if @@langid != 0 45 begin 46 if not exists ( 47 select * from master.dbo.sysmessages where error 48 between 17100 and 17109 49 and langid = @@langid) 50 select @sptlang = 0 51 end 52 53 create table #oledb_results_table 54 ( 55 PROCEDURE_CATALOG varchar(32) null, 56 PROCEDURE_SCHEMA varchar(32) null, 57 PROCEDURE_NAME varchar(255) null, 58 PARAMETER_NAME varchar(255) null, 59 ORDINAL_POSITION smallint null, 60 PARAMETER_TYPE smallint null, 61 PARAMETER_HASDEFAULT bit not null, 62 PARAMETER_DEFAULT varchar(255) null, 63 IS_NULLABLE bit not null, 64 DATA_TYPE smallint null, 65 CHARACTER_MAXIMUM_LENGTH int null, 66 CHARACTER_OCTET_LENGTH int null, 67 NUMERIC_PRECISION smallint null, 68 NUMERIC_PRECISION_RADIX smallint null, 69 NUMERIC_SCALE smallint null, 70 DESCRIPTION varchar(255) null, 71 TYPE_NAME varchar(32) null, 72 LOCAL_TYPE_NAME varchar(32) null, 73 74 ) 75 76 /* If column name not supplied, match all */ 77 if @parameter_name is null 78 select @parameter_name = '%' 79 80 /* The qualifier must be the name of current database or null */ 81 if @procedure_catalog is not null 82 begin 83 if db_name() != @procedure_catalog 84 begin 85 if @procedure_catalog = '' 86 begin 87 /* in this case, we need to return an empty result 88 ** set because the user has requested a database with 89 ** an empty name 90 */ 91 select @procedure_name = '' 92 select @procedure_schema = '' 93 end 94 else 95 begin 96 /* 97 ** 18039, Table qualifier must be name of current database 98 */ 99 exec sp_getmessage 18039, @msg output 100 print @msg 101 return 102 end 103 end 104 end 105 106 107 /* first we need to extract the procedure group number, if one exists */ 108 select @semi_position = charindex(';', @procedure_name) 109 if (@semi_position > 0) 110 begin /* If group number separator (;) found */ 111 select @group_num = convert(int, substring(@procedure_name, 112 @semi_position + 1, 2)) 113 select @procedure_name = substring(@procedure_name, 1, 114 @semi_position - 1) 115 end 116 else 117 begin /* No group separator, so default to group number of 1 */ 118 select @group_num = 1 119 end 120 121 /* character and binary datatypes */ 122 select @char_bin_types = 123 char(47) + char(39) + char(45) + char(37) + char(35) + char(34) 124 125 if @procedure_schema is null 126 begin /* If unqualified procedure name */ 127 select @full_procedure_name = @procedure_name 128 end 129 else 130 begin /* Qualified procedure name */ 131 select @full_procedure_name = @procedure_schema + '.' + @procedure_name 132 end 133 134 /* 135 ** If the @parameter_name parameter is "RETURN_VALUE" and this is a sqlj 136 ** function, then we should be looking for column name "Return Type" 137 */ 138 if @parameter_name = "RETURN_VALUE" 139 and exists (select 1 from sysobjects 140 where id = object_id(@full_procedure_name) 141 and type = 'F') 142 begin 143 select @parameter_name = "Return Type" 144 end 145 146 /* Get Object ID */ 147 select @procedure_id = object_id(@full_procedure_name) 148 149 if ((charindex('%', @full_procedure_name) = 0) and 150 (charindex('_', @full_procedure_name) = 0) and 151 @procedure_id != 0) 152 begin 153 /* 154 ** this block is for the case where there is no pattern 155 ** matching required for the procedure name 156 */ 157 insert #oledb_results_table 158 select /* INTn, FLOATn, DATETIMEn and MONEYn types */ 159 PROCEDURE_CATALOG = db_name(), 160 PROCEDURE_SCHEMA = user_name(o.uid), 161 PROCEDURE_NAME = o.name, 162 PARAMETER_NAME = 163 case 164 when c.name = 'Return Type' then 'RETURN_VALUE' 165 else c.name 166 end, 167 ORDINAL_POSITION = convert(int, c.colid), 168 PARAMETER_TYPE = 169 case 170 when c.name = 'Return Type' 171 then convert(smallint, 4) /*DBPARAMTYPE_RETURNVALUE*/ 172 when c.status2 = 1 173 then convert(smallint, 1) /*DBPARAMTYPE_INPUT*/ 174 when c.status2 = 2 175 then convert(smallint, 3) /*DBPARAMTYPE_OUTPUT*/ 176 when c.status2 = 4 177 then convert(smallint, 2) /*DBPARAMTYPE_INPUTOUTPUT*/ 178 else null 179 end, 180 PARAMETER_HASDEFAULT = convert(bit, 0), 181 PARAMETER_DEFAULT = convert(varchar(255), null), 182 IS_NULLABLE = /* set nullability from status flag */ 183 convert(smallint, 1), 184 185 DATA_TYPE = 0, 186 187 CHARACTER_MAXIMUM_LENGTH = isnull(convert(int, c.prec), 188 isnull(d.data_precision, convert(int, c.length))) 189 + isnull(d.aux, convert(int, 190 ascii(substring("???AAAFFFCKFOLS", 191 2 * (d.ss_dtype % 35 + 1) 192 + 2 - 8 / c.length, 1)) 193 - 60)), 194 CHARACTER_OCTET_LENGTH = 195 /* 196 ** check if in the list 197 ** if so, return a 1 and multiply it by the precision 198 ** if not, return a 0 and multiply it by the precision 199 */ 200 convert(smallint, 201 substring('0111111', 202 charindex(char(c.type), 203 @char_bin_types) + 1, 1)) * 204 /* calculate the precision */ 205 isnull(convert(int, c.prec), 206 isnull(convert(int, d.data_precision), 207 convert(int, c.length))) 208 + isnull(d.aux, convert(int, 209 ascii(substring('???AAAFFFCKFOLS', 210 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 211 212 NUMERIC_PRECISION = d.data_precision, 213 NUMERIC_PRECISION_RADIX = d.numeric_radix, 214 NUMERIC_SCALE = isnull(convert(smallint, c.scale), 215 convert(smallint, d.numeric_scale)) + 216 convert(smallint, 217 isnull(d.aux, ascii(substring("<<<<<<<<<<<<<<?", 218 2 * (d.ss_dtype % 35 + 1) 219 + 2 - 8 / c.length, 220 1)) - 60)), 221 222 DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */ 223 TYPE_NAME = rtrim(substring(d.type_name, 224 1 + isnull(d.aux, 225 ascii(substring("III<<<MMMI<<A<A", 226 2 * (d.ss_dtype % 35 + 1) 227 + 2 - 8 / c.length, 228 1)) - 60), 18)), 229 LOCAL_TYPE_NAME = rtrim(substring(d.type_name, 230 1 + isnull(d.aux, 231 ascii(substring("III<<<MMMI<<A<A", 232 2 * (d.ss_dtype % 35 + 1) 233 + 2 - 8 / c.length, 234 1)) - 60), 18)) 235 236 237 238 239 240 from 241 syscolumns c, 242 sysobjects o, 243 sybsystemprocs.dbo.spt_datatype_info d, 244 systypes t, 245 sysprocedures p 246 247 where 248 o.id = @procedure_id 249 and c.id = o.id 250 and c.type = d.ss_dtype 251 and c.name like @parameter_name 252 and d.ss_dtype in (111, 109, 38, 110) /* Just *N types */ 253 and c.number = @group_num 254 255 union 256 select 257 PROCEDURE_CATALOG = db_name(), 258 PROCEDURE_SCHEMA = user_name(o.uid), 259 PROCEDURE_NAME = o.name, 260 PARAMETER_NAME = 'RETURN_VALUE', 261 ORDINAL_POSITION = convert(tinyint, 0), 262 PARAMETER_TYPE = convert(smallint, 4), /* return parameter */ 263 PARAMETER_HASDEFAULT = convert(bit, 0), 264 PARAMETER_DEFAULT = convert(varchar(255), null), 265 IS_NULLABLE = convert(smallint, 1), 266 DATA_TYPE = 0, 267 CHARACTER_MAXIMUM_LENGTH = isnull(d.data_precision, convert(int, d.length)) 268 + isnull(d.aux, convert(int, 269 ascii(substring("???AAAFFFCKFOLS", 270 2 * (d.ss_dtype % 35 + 1) 271 + 2 - 8 / d.length, 1)) 272 - 60)), 273 CHARACTER_OCTET_LENGTH = NULL, 274 NUMERIC_PRECISION = d.data_precision, 275 NUMERIC_PRECISION_RADIX = d.numeric_radix, 276 NUMERIC_SCALE = d.numeric_scale + convert(smallint, 277 isnull(d.aux, 278 ascii(substring("<<<<<<<<<<<<<<?", 279 2 * (d.ss_dtype % 35 + 1) 280 + 2 - 8 / d.length, 281 1)) - 60)), 282 283 DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */ 284 TYPE_NAME = d.type_name, 285 LOCAL_TYPE_NAME = d.type_name 286 287 288 289 290 291 292 from 293 sysobjects o, 294 sybsystemprocs.dbo.spt_datatype_info d, 295 systypes t 296 297 where 298 o.id = @procedure_id 299 and d.ss_dtype = 56 /* int for return code */ 300 and t.type = 56 301 and o.type = 'P' 302 and (@parameter_name = '%' or @parameter_name = 'RETURN_VALUE') 303 304 union 305 select /* All other types including user data types */ 306 PROCEDURE_CATALOG = db_name(), 307 PROCEDURE_SCHEMA = user_name(o.uid), 308 PROCEDURE_NAME = o.name, 309 PARAMETER_NAME = 310 case 311 when c.name = 'Return Type' then 'RETURN_VALUE' 312 else c.name 313 end, 314 ORDINAL_POSITION = convert(int, c.colid), 315 PARAMETER_TYPE = 316 case 317 when c.name = 'Return Type' 318 then convert(smallint, 4) /*DBPARAMTYPE_RETURNVALUE*/ 319 when c.status2 = 1 320 then convert(smallint, 1) /*DBPARAMTYPE_INPUT*/ 321 when c.status2 = 2 322 then convert(smallint, 3) /*DBPARAMTYPE_OUTPUT*/ 323 when c.status2 = 4 324 then convert(smallint, 2) /*DBPARAMTYPE_INPUTOUTPUT*/ 325 else null 326 end, 327 PARAMETER_HASDEFAULT = convert(bit, 0), 328 PARAMETER_DEFAULT = convert(varchar(255), null), 329 IS_NULLABLE = convert(smallint, 1), 330 DATA_TYPE = 0, 331 CHARACTER_MAXIMUM_LENGTH = 332 case 333 when d.data_precision = 0 334 then convert(int, 0) 335 else 336 isnull(convert(int, c.prec), 337 isnull(d.data_precision, convert(int, c.length))) 338 + isnull(d.aux, convert(int, 339 ascii(substring("???AAAFFFCKFOLS", 340 2 * (d.ss_dtype % 35 + 1) 341 + 2 - 8 / c.length, 1)) 342 - 60)) 343 end, 344 CHARACTER_OCTET_LENGTH = 345 /* 346 ** check if in the list 347 ** if so, return a 1 and multiply it by the precision 348 ** if not, return a 0 and multiply it by the precision 349 */ 350 convert(smallint, 351 substring('0111111', 352 charindex(char(c.type), 353 @char_bin_types) + 1, 1)) * 354 /* calculate the precision */ 355 isnull(convert(int, c.prec), 356 isnull(convert(int, d.data_precision), 357 convert(int, c.length))) 358 + isnull(d.aux, convert(int, 359 ascii(substring('???AAAFFFCKFOLS', 360 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 361 362 363 NUMERIC_PRECISION = d.data_precision, 364 NUMERIC_PRECISION_RADIX = d.numeric_radix, 365 NUMERIC_SCALE = isnull(convert(smallint, c.scale), 366 convert(smallint, d.numeric_scale)) 367 + convert(smallint, 368 isnull(d.aux, 369 ascii(substring("<<<<<<<<<<<<<<?", 370 2 * (d.ss_dtype % 35 + 1) 371 + 2 - 8 / c.length, 372 1)) - 60)), 373 374 /* set nullability from status flag */ 375 376 DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */ 377 TYPE_NAME = 378 case 379 when t.name = 'extended type' 380 then isnull(get_xtypename(c.xtype, c.xdbid), 381 t.name) 382 when t.type = 58 383 then "smalldatetime" 384 when t.usertype in (44, 45, 46) 385 then "unsigned " + substring(t.name, 386 charindex("u", t.name) + 1, 387 charindex("t", t.name)) 388 else 389 t.name 390 end, 391 LOCAL_TYPE_NAME = case when t.name = 'extended type' 392 then isnull(get_xtypename(c.xtype, c.xdbid), t.name) 393 when t.type = 58 then "smalldatetime" 394 when t.usertype in (44, 45, 46) 395 then "unsigned " + substring(t.name, 396 charindex("u", t.name) + 1, 397 charindex("t", t.name)) 398 else t.name 399 end 400 401 402 403 404 from 405 syscolumns c, 406 sysobjects o, 407 sybsystemprocs.dbo.spt_datatype_info d, 408 systypes t 409 410 where 411 o.id = @procedure_id 412 and c.id = o.id 413 and c.type *= d.ss_dtype 414 and c.usertype *= t.usertype 415 and c.name like @parameter_name 416 and c.number = @group_num 417 and d.ss_dtype not in (111, 109, 38, 110) /* No *N types */ 418 419 420 order by convert(int, colid) 421 end 422 else 423 begin 424 /* 425 ** this block is for the case where there IS pattern 426 ** matching done on the table name 427 */ 428 if @procedure_schema is null 429 select @procedure_schema = '%' 430 insert #oledb_results_table 431 select /* INTn, FLOATn, DATETIMEn and MONEYn types */ 432 PROCEDURE_CATALOG = db_name(), 433 PROCEDURE_SCHEMA = user_name(o.uid), 434 PROCEDURE_NAME = o.name, 435 PARAMETER_NAME = 436 case 437 when c.name = 'Return Type' then 'RETURN_VALUE' 438 else c.name 439 end, 440 ORDINAL_POSITION = convert(int, c.colid), 441 PARAMETER_TYPE = 442 case 443 when c.name = 'Return Type' 444 then convert(smallint, 4) /*DBPARAMTYPE_RETURNVALUE*/ 445 when c.status2 = 1 446 then convert(smallint, 1) /*DBPARAMTYPE_INPUT*/ 447 when c.status2 = 2 448 then convert(smallint, 3) /*DBPARAMTYPE_OUTPUT*/ 449 when c.status2 = 4 450 then convert(smallint, 2) /*DBPARAMTYPE_INPUTOUTPUT*/ 451 else null 452 end, 453 PARAMETER_HASDEFAULT = convert(bit, 0), 454 PARAMETER_DEFAULT = convert(varchar(255), null), 455 IS_NULLABLE = convert(smallint, 1), 456 DATA_TYPE = 0, 457 CHARACTER_MAXIMUM_LENGTH = isnull(convert(int, c.prec), 458 isnull(d.data_precision, convert(int, c.length))) 459 + isnull(d.aux, convert(int, 460 ascii(substring("???AAAFFFCKFOLS", 461 2 * (d.ss_dtype % 35 + 1) 462 + 2 - 8 / c.length, 1)) 463 - 60)), 464 CHARACTER_OCTET_LENGTH = 465 /* 466 ** check if in the list 467 ** if so, return a 1 and multiply it by the precision 468 ** if not, return a 0 and multiply it by the precision 469 */ 470 convert(smallint, 471 substring('0111111', 472 charindex(char(c.type), 473 @char_bin_types) + 1, 1)) * 474 /* calculate the precision */ 475 isnull(convert(int, c.prec), 476 isnull(convert(int, d.data_precision), 477 convert(int, c.length))) 478 + isnull(d.aux, convert(int, 479 ascii(substring('???AAAFFFCKFOLS', 480 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 481 482 483 NUMERIC_PRECISION = d.data_precision, 484 NUMERIC_PRECISION_RADIX = d.numeric_radix, 485 NUMERIC_SCALE = isnull(convert(smallint, c.scale), 486 convert(smallint, d.numeric_scale)) 487 + convert(smallint, 488 isnull(d.aux, 489 ascii(substring("<<<<<<<<<<<<<<?", 490 2 * (d.ss_dtype % 35 + 1) 491 + 2 - 8 / c.length, 492 1)) - 60)), 493 494 /* set nullability from status flag */ 495 496 DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */ 497 TYPE_NAME = rtrim(substring(d.type_name, 498 1 + isnull(d.aux, 499 ascii(substring("III<<<MMMI<<A<A", 500 2 * (d.ss_dtype % 35 + 1) 501 + 2 - 8 / c.length, 502 1)) - 60), 18)), 503 LOCAL_TYPE_NAME = rtrim(substring(d.type_name, 504 1 + isnull(d.aux, 505 ascii(substring("III<<<MMMI<<A<A", 506 2 * (d.ss_dtype % 35 + 1) 507 + 2 - 8 / c.length, 508 1)) - 60), 18)) 509 510 511 512 513 from 514 syscolumns c, 515 sysobjects o, 516 sybsystemprocs.dbo.spt_datatype_info d, 517 systypes t 518 519 where 520 o.name like @procedure_name 521 and user_name(o.uid) like @procedure_schema 522 and o.id = c.id 523 and c.type = d.ss_dtype 524 and c.name like @parameter_name 525 526 /* Just procs & sqlj procs and funcs */ 527 and o.type in ('P', 'F') 528 and d.ss_dtype in (111, 109, 38, 110) /* Just *N types */ 529 union 530 select distinct 531 PROCEDURE_CATALOG = db_name(), 532 PROCEDURE_SCHEMA = user_name(o.uid), 533 PROCEDURE_NAME = o.name, 534 PARAMETER_NAME = 'RETURN_VALUE', 535 ORDINAL_POSITION = convert(tinyint, 0), 536 PARAMETER_TYPE = convert(smallint, 4), /* return parameter */ 537 PARAMETER_HASDEFAULT = convert(bit, 0), 538 PARAMETER_DEFAULT = convert(varchar(255), null), 539 IS_NULLABLE = convert(smallint, 1), 540 DATA_TYPE = 0, 541 CHARACTER_MAXIMUM_LENGTH = isnull(d.data_precision, convert(int, d.length)) 542 + isnull(d.aux, convert(int, 543 ascii(substring("???AAAFFFCKFOLS", 544 2 * (d.ss_dtype % 35 + 1) 545 + 2 - 8 / d.length, 1)) 546 - 60)), 547 CHARACTER_OCTET_LENGTH = NULL, 548 549 NUMERIC_PRECISION = d.data_precision, 550 NUMERIC_PRECISION_RADIX = d.numeric_radix, 551 NUMERIC_SCALE = d.numeric_scale + convert(smallint, 552 isnull(d.aux, 553 ascii(substring("<<<<<<<<<<<<<<?", 554 2 * (d.ss_dtype % 35 + 1) 555 + 2 - 8 / d.length, 556 1)) - 60)), 557 558 559 DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */ 560 TYPE_NAME = d.type_name, 561 LOCAL_TYPE_NAME = d.type_name 562 563 564 565 from 566 sysobjects o, 567 sybsystemprocs.dbo.spt_datatype_info d, 568 systypes t, 569 sysprocedures p 570 571 where 572 o.name like @procedure_name 573 and user_name(o.uid) like @procedure_schema 574 and d.ss_dtype = 56 /* int for return code */ 575 and t.type = 56 576 and o.type = 'P' /* Just Procedures */ 577 and p.id = o.id 578 and 'RETURN_VALUE' like @parameter_name 579 union 580 select /* All other types including user data types */ 581 PROCEDURE_CATALOG = db_name(), 582 PROCEDURE_SCHEMA = user_name(o.uid), 583 PROCEDURE_NAME = o.name, 584 PARAMETER_NAME = 585 case 586 when c.name = 'Return Type' then 'RETURN_VALUE' 587 else c.name 588 end, 589 ORDINAL_POSITION = convert(int, c.colid), 590 PARAMETER_TYPE = 591 case 592 when c.name = 'Return Type' 593 then convert(smallint, 4) 594 when c.status2 = 1 595 then convert(smallint, 1) 596 when c.status2 = 2 597 then convert(smallint, 3) 598 when c.status2 = 4 599 then convert(smallint, 2) 600 else null 601 end, 602 PARAMETER_HASDEFAULT = convert(bit, 0), 603 PARAMETER_DEFAULT = convert(varchar(255), null), 604 IS_NULLABLE = convert(smallint, 1), 605 DATA_TYPE = 0, 606 CHARACTER_MAXIMUM_LENGTH = 607 case 608 when d.data_precision = 0 609 then convert(int, 0) 610 else 611 isnull(convert(int, c.prec), 612 isnull(d.data_precision, convert(int, c.length))) 613 + isnull(d.aux, 614 convert(int, 615 ascii(substring("???AAAFFFCKFOLS", 616 2 * (d.ss_dtype % 35 + 1) 617 + 2 - 8 / c.length, 1)) 618 - 60)) 619 end, 620 CHARACTER_OCTET_LENGTH = 621 /* 622 ** check if in the list 623 ** if so, return a 1 and multiply it by the precision 624 ** if not, return a 0 and multiply it by the precision 625 */ 626 convert(smallint, 627 substring('0111111', 628 charindex(char(c.type), 629 @char_bin_types) + 1, 1)) * 630 /* calculate the precision */ 631 isnull(convert(int, c.prec), 632 isnull(convert(int, d.data_precision), 633 convert(int, c.length))) 634 + isnull(d.aux, convert(int, 635 ascii(substring('???AAAFFFCKFOLS', 636 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 637 638 639 NUMERIC_PRECISION = d.data_precision, 640 NUMERIC_PRECISION_RADIX = d.numeric_radix, 641 NUMERIC_SCALE = isnull(convert(smallint, c.scale), 642 convert(smallint, d.numeric_scale)) 643 + convert(smallint, 644 isnull(d.aux, 645 ascii(substring("<<<<<<<<<<<<<<?", 646 2 * (d.ss_dtype % 35 + 1) 647 + 2 - 8 / c.length, 648 1)) - 60)), 649 650 /* set nullability from status flag */ 651 652 DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */ 653 TYPE_NAME = 654 case 655 when t.name = 'extended type' 656 then isnull(get_xtypename(c.xtype, c.xdbid), 657 t.name) 658 when t.type = 58 659 then "smalldatetime" 660 when t.usertype in (44, 45, 46) 661 then "unsigned " + substring(t.name, 662 charindex("u", t.name) + 1, 663 charindex("t", t.name)) 664 else 665 t.name 666 end, 667 LOCAL_TYPE_NAME = 668 case 669 when t.name = 'extended type' 670 then isnull(get_xtypename(c.xtype, c.xdbid), 671 t.name) 672 when t.type = 58 673 then "smalldatetime" 674 when t.usertype in (44, 45, 46) 675 then "unsigned " + substring(t.name, 676 charindex("u", t.name) + 1, 677 charindex("t", t.name)) 678 else 679 t.name 680 end 681 682 683 from 684 syscolumns c, 685 sysobjects o, 686 sybsystemprocs.dbo.spt_datatype_info d, 687 systypes t 688 689 where 690 o.name like @procedure_name 691 and user_name(o.uid) like @procedure_schema 692 and o.id = c.id 693 and c.type *= d.ss_dtype 694 and c.usertype *= t.usertype 695 696 /* Just procs & sqlj procs and funcs */ 697 and o.type in ('P', 'F') 698 and c.name like @parameter_name 699 and d.ss_dtype not in (111, 109, 38, 110) /* No *N types */ 700 701 702 order by PROCEDURE_SCHEMA, PROCEDURE_NAME, convert(int, colid) 703 end 704 705 706 update #oledb_results_table set o.DATA_TYPE = m.data_type from 707 sybsystemprocs.dbo.spt_sybdrv m, #oledb_results_table o 708 where o.TYPE_NAME = m.type_name 709 if (@is_ado = 2) 710 begin 711 /* Adaptive Server has expanded all '*' elements in the following statement */ select #oledb_results_table.PROCEDURE_CATALOG, #oledb_results_table.PROCEDURE_SCHEMA, #oledb_results_table.PROCEDURE_NAME, #oledb_results_table.PARAMETER_NAME, #oledb_results_table.ORDINAL_POSITION, #oledb_results_table.PARAMETER_TYPE, #oledb_results_table.PARAMETER_HASDEFAULT, #oledb_results_table.PARAMETER_DEFAULT, #oledb_results_table.IS_NULLABLE, #oledb_results_table.DATA_TYPE, #oledb_results_table.CHARACTER_MAXIMUM_LENGTH, #oledb_results_table.CHARACTER_OCTET_LENGTH, #oledb_results_table.NUMERIC_PRECISION, #oledb_results_table.NUMERIC_PRECISION_RADIX, #oledb_results_table.NUMERIC_SCALE, #oledb_results_table.DESCRIPTION, #oledb_results_table.TYPE_NAME, #oledb_results_table.LOCAL_TYPE_NAME from #oledb_results_table order by PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME 712 end 713 else if (@is_ado = 1) 714 begin 715 select 716 PROCEDURE_CATALOG, 717 PROCEDURE_SCHEMA, 718 PROCEDURE_NAME, 719 PARAMETER_NAME, 720 ORDINAL_POSITION, 721 PARAMETER_TYPE, 722 PARAMETER_HASDEFAULT, 723 PARAMETER_DEFAULT, 724 IS_NULLABLE, 725 DATA_TYPE, 726 CHARACTER_MAXIMUM_LENGTH, 727 CHARACTER_OCTET_LENGTH, 728 NUMERIC_PRECISION, 729 NUMERIC_SCALE, 730 DESCRIPTION, 731 TYPE_NAME, 732 LOCAL_TYPE_NAME 733 from #oledb_results_table order by PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME 734 735 end 736 737 if (@startedInTransaction = 1) 738 rollback transaction oledb_keep_temptable_tx 739 740
exec sp_procxmode 'sp_oledb_getprocedurecolumns', 'AnyMode' go Grant Execute on sp_oledb_getprocedurecolumns to public go
RESULT SETS | |
sp_oledb_getprocedurecolumns_rset_002 | |
sp_oledb_getprocedurecolumns_rset_001 |
DEPENDENCIES |
PROCS AND TABLES USED reads table sybsystemprocs..syscolumns ![]() reads table sybsystemprocs..systypes ![]() read_writes table tempdb..#oledb_results_table (1) reads table sybsystemprocs..sysprocedures ![]() reads table master..sysmessages (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() reads table sybsystemprocs..spt_sybdrv ![]() reads table sybsystemprocs..sysobjects ![]() reads table sybsystemprocs..spt_datatype_info ![]() |