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