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