| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_columns | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/%M% %I% %G% " */ 3 /* 10.0 07/20/93 sproc/columns */ 4 5 6 /* This is the version for servers which support UNION */ 7 8 /* This routine is intended for support of ODBC connectivity. Under no 9 ** circumstances should changes be made to this routine unless they are 10 ** to fix ODBC related problems. All other users are at there own risk! 11 ** 12 ** Please be aware that any changes made to this file (or any other ODBC 13 ** support routine) will require Sybase to recertify the SQL server as 14 ** ODBC compliant. This process is currently being managed internally 15 ** by the "Interoperability Engineering Technology Solutions Group" here 16 ** within Sybase. 17 */ 18 19 CREATE PROCEDURE sp_columns( 20 @table_name varchar(257), 21 @table_owner varchar(255) = null, 22 @table_qualifier varchar(32) = null, 23 @column_name varchar(257) = null) 24 AS 25 declare @msg varchar(1024) 26 declare @full_table_name varchar(511) 27 declare @table_id int 28 declare @char_bin_types varchar(30) 29 30 set transaction isolation level 1 31 32 /* character and binary datatypes */ 33 select @char_bin_types = 34 char(47) + char(39) + char(45) + char(37) + char(35) + char(34) 35 36 if @column_name is null /* If column name not supplied, match all */ 37 select @column_name = '%' 38 39 /* Check if the current database is the same as the one provided */ 40 if @table_qualifier is not null 41 begin 42 if db_name() != @table_qualifier 43 begin /* 44 ** If qualifier doesn't match current database 45 ** 18039, Table qualifier must be name of current database 46 */ 47 raiserror 18039 48 return (1) 49 end 50 end 51 52 if @table_name is null 53 begin /* If table name not supplied, match all */ 54 select @table_name = '%' 55 end 56 57 if @table_owner is null 58 begin /* If unqualified table name */ 59 SELECT @full_table_name = @table_name 60 end 61 else 62 begin /* Qualified table name */ 63 SELECT @full_table_name = @table_owner + '.' + @table_name 64 end 65 66 /* Get Object ID */ 67 SELECT @table_id = object_id(@full_table_name) 68 69 70 /* If the table name parameter is valid, get the information */ 71 if ((charindex('%', @full_table_name) = 0) and 72 (charindex('_', @full_table_name) = 0) and 73 @table_id != 0) 74 begin 75 SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */ 76 table_qualifier = DB_NAME(), 77 table_owner = USER_NAME(o.uid), 78 table_name = o.name, 79 column_name = c.name, 80 data_type = d.data_type + convert(smallint, 81 isnull(d.aux, 82 ascii(substring("666AAA@@@CB??GG", 83 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 84 - 60)), 85 type_name = rtrim(substring(isnull(stuff(d.type_name, 86 (c.status & 128) / 128, 87 char_length(d.type_name), 88 "numeric identity"), d.type_name), 89 1 + isnull(d.aux, 90 ascii(substring("III<<<MMMI<<A<A", 91 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 92 1)) - 60), 18)), 93 "precision" = isnull(convert(int, c.prec), 94 isnull(convert(int, d.data_precision), 95 convert(int, c.length))) 96 + isnull(d.aux, convert(int, 97 ascii(substring("???AAAFFFCKFOLS", 98 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 99 length = isnull(convert(int, c.length), 100 convert(int, d.length)) + 101 convert(int, isnull(d.aux, 102 ascii(substring("AAA<BB<DDDHJSPP", 103 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 104 1)) - 64)), 105 scale = isnull(convert(smallint, c.scale), 106 convert(smallint, d.numeric_scale)) 107 + convert(smallint, 108 isnull(d.aux, 109 ascii(substring("<<<<<<<<<<<<<<?", 110 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 111 1)) - 60)), 112 radix = d.numeric_radix, 113 nullable = /* set nullability from status flag */ 114 convert(smallint, convert(bit, c.status & 8)), 115 remarks = convert(varchar(254), null), /* Remarks are NULL */ 116 ss_data_type = c.type, 117 colid = c.colid, 118 column_def = NULL, 119 sql_data_type = isnull(d.sql_data_type, 120 d.data_type + convert(smallint, 121 isnull(d.aux, 122 ascii(substring("666AAA@@@CB??GG", 123 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 124 - 60))), 125 sql_datetime_sub = NULL, 126 /* 127 ** if the datatype is of type CHAR or BINARY 128 ** then set char_octet_length to the same value 129 ** assigned in the "prec" column. 130 ** 131 ** The first part of the logic is: 132 ** 133 ** if(c.type is in (47, 39, 45, 37, 35, 34)) 134 ** set char_octet_length = prec; 135 ** else 136 ** set char_octet_length = 0; 137 */ 138 char_octet_length = 139 /* 140 ** check if in the list 141 ** if so, return a 1 and multiply it by the precision 142 ** if not, return a 0 and multiply it by the precision 143 */ 144 convert(smallint, 145 substring('0111111', 146 charindex(char(c.type), 147 @char_bin_types) + 1, 1)) * 148 /* calculate the precision */ 149 isnull(convert(int, c.prec), 150 isnull(convert(int, d.data_precision), 151 convert(int, c.length))) 152 + isnull(d.aux, convert(int, 153 ascii(substring('???AAAFFFCKFOLS', 154 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 155 ordinal_position = c.colid, 156 is_nullable = rtrim(substring('NO YES', 157 (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)) 158 159 FROM 160 syscolumns c, 161 sysobjects o, 162 sybsystemprocs.dbo.spt_datatype_info d, 163 systypes t 164 WHERE 165 o.id = @table_id 166 AND c.id = o.id 167 /* 168 ** We use syscolumn.usertype instead of syscolumn.type 169 ** to do join with systypes.usertype. This is because 170 ** for a column which allows null, type stores its 171 ** Server internal datatype whereas usertype still 172 ** stores its user defintion datatype. For an example, 173 ** a column of type 'decimal NULL', its usertype = 26, 174 ** representing decimal whereas its type = 106 175 ** representing decimaln. nullable in the select list 176 ** already tells user whether the column allows null. 177 ** In the case of user defining datatype, this makes 178 ** more sense for the user. 179 */ 180 AND c.usertype = t.usertype 181 AND t.type = d.ss_dtype 182 AND c.name like @column_name 183 AND d.ss_dtype IN (111, 109, 38, 110, 43) /* Just *N types */ 184 AND c.usertype < 100 /* No user defined types */ 185 UNION 186 SELECT /* All other types including user data types */ 187 table_qualifier = DB_NAME(), 188 table_owner = USER_NAME(o.uid), 189 table_name = o.name, 190 column_name = c.name, 191 data_type = d.data_type + convert(smallint, 192 isnull(d.aux, 193 ascii(substring("666AAA@@@CB??GG", 194 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 195 - 60)), 196 type_name = rtrim(substring(isnull(stuff(d.type_name, 197 (c.status & 128) / 128, 198 char_length(d.type_name), 199 "numeric identity"), d.type_name), 200 1 + isnull(d.aux, 201 ascii(substring("III<<<MMMI<<A<A", 202 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 203 1)) - 60), 18)), 204 "precision" = isnull(convert(int, c.prec), 205 isnull(convert(int, d.data_precision), 206 convert(int, c.length))) 207 + isnull(d.aux, convert(int, 208 ascii(substring("???AAAFFFCKFOLS", 209 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 210 length = isnull(convert(int, c.length), 211 convert(int, d.length)) + 212 convert(int, isnull(d.aux, 213 ascii(substring("AAA<BB<DDDHJSPP", 214 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 215 1)) - 64)), 216 scale = isnull(convert(smallint, c.scale), 217 convert(smallint, d.numeric_scale)) + 218 convert(smallint, isnull(d.aux, 219 ascii(substring("<<<<<<<<<<<<<<?", 220 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 221 1)) - 60)), 222 radix = d.numeric_radix, 223 nullable = /* set nullability from status flag */ 224 convert(smallint, convert(bit, c.status & 8)), 225 remarks = convert(varchar(254), null), /* Remarks are NULL */ 226 ss_data_type = c.type, 227 colid = c.colid, 228 column_def = NULL, 229 sql_data_type = isnull(d.sql_data_type, 230 d.data_type + convert(smallint, 231 isnull(d.aux, 232 ascii(substring("666AAA@@@CB??GG", 233 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 234 - 60))), 235 sql_datetime_sub = NULL, 236 /* 237 ** if the datatype is of type CHAR or BINARY 238 ** then set char_octet_length to the same value 239 ** assigned in the "prec" column. 240 ** 241 ** The first part of the logic is: 242 ** 243 ** if(c.type is in (47, 39, 45, 37, 35, 34)) 244 ** set char_octet_length = prec; 245 ** else 246 ** set char_octet_length = 0; 247 */ 248 char_octet_length = 249 /* 250 ** check if in the list 251 ** if so, return a 1 and multiply it by the precision 252 ** if not, return a 0 and multiply it by the precision 253 */ 254 convert(smallint, 255 substring('0111111', 256 charindex(char(c.type), 257 @char_bin_types) + 1, 1)) * 258 /* calculate the precision */ 259 isnull(convert(int, c.prec), 260 isnull(convert(int, d.data_precision), 261 convert(int, c.length))) 262 + isnull(d.aux, convert(int, 263 ascii(substring('???AAAFFFCKFOLS', 264 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 265 ordinal_position = c.colid, 266 is_nullable = rtrim(substring('NO YES', 267 (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)) 268 269 FROM 270 syscolumns c, 271 sysobjects o, 272 sybsystemprocs.dbo.spt_datatype_info d, 273 systypes t 274 WHERE 275 o.id = @table_id 276 AND c.id = o.id 277 /* 278 ** We use syscolumn.usertype instead of syscolumn.type 279 ** to do join with systypes.usertype. This is because 280 ** for a column which allows null, type stores its 281 ** Server internal datatype whereas usertype still 282 ** stores its user defintion datatype. For an example, 283 ** a column of type 'decimal NULL', its usertype = 26, 284 ** representing decimal whereas its type = 106 285 ** representing decimaln. nullable in the select list 286 ** already tells user whether the column allows null. 287 ** In the case of user defining datatype, this makes 288 ** more sense for the user. 289 */ 290 AND c.usertype = t.usertype 291 /* 292 ** We need a equality join with 293 ** sybsystemprocs.dbo.spt_datatype_info here so that 294 ** there is only one qualified row returned from 295 ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding 296 ** duplicates. 297 */ 298 AND t.type = d.ss_dtype 299 AND c.name like @column_name 300 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 43) /* No *N types */ 301 302 OR c.usertype >= 100) /* User defined types */ 303 304 ORDER BY colid 305 end 306 else 307 begin 308 /* 309 ** This block is for the case where there IS pattern 310 ** matching done on the table name. 311 */ 312 if @table_owner is null /* If owner not supplied, match all */ 313 select @table_owner = '%' 314 315 SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */ 316 table_qualifier = DB_NAME(), 317 table_owner = USER_NAME(o.uid), 318 table_name = o.name, 319 column_name = c.name, 320 data_type = d.data_type + convert(smallint, 321 isnull(d.aux, 322 ascii(substring("666AAA@@@CB??GG", 323 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 324 - 60)), 325 type_name = rtrim(substring(isnull(stuff(d.type_name, 326 (c.status & 128) / 128, 327 char_length(d.type_name), 328 "numeric identity"), d.type_name), 329 1 + isnull(d.aux, 330 ascii(substring("III<<<MMMI<<A<A", 331 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 332 1)) - 60), 18)), 333 "precision" = isnull(convert(int, c.prec), 334 isnull(convert(int, d.data_precision), 335 convert(int, c.length))) 336 + isnull(d.aux, convert(int, 337 ascii(substring("???AAAFFFCKFOLS", 338 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 339 length = isnull(convert(int, c.length), 340 convert(int, d.length)) + 341 convert(int, isnull(d.aux, 342 ascii(substring("AAA<BB<DDDHJSPP", 343 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 344 1)) - 64)), 345 scale = isnull(convert(smallint, c.scale), 346 convert(smallint, d.numeric_scale)) + 347 convert(smallint, isnull(d.aux, 348 ascii(substring("<<<<<<<<<<<<<<?", 349 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 350 1)) - 60)), 351 radix = d.numeric_radix, 352 nullable = /* set nullability from status flag */ 353 convert(smallint, convert(bit, c.status & 8)), 354 remarks = convert(varchar(254), null), /* Remarks are NULL */ 355 ss_data_type = c.type, 356 colid = c.colid, 357 column_def = NULL, 358 sql_data_type = isnull(d.sql_data_type, 359 d.data_type + convert(smallint, 360 isnull(d.aux, 361 ascii(substring("666AAA@@@CB??GG", 362 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 363 - 60))), 364 sql_datetime_sub = NULL, 365 /* 366 ** if the datatype is of type CHAR or BINARY 367 ** then set char_octet_length to the same value 368 ** assigned in the "prec" column. 369 ** 370 ** The first part of the logic is: 371 ** 372 ** if(c.type is in (47, 39, 45, 37, 35, 34)) 373 ** set char_octet_length = prec; 374 ** else 375 ** set char_octet_length = 0; 376 */ 377 char_octet_length = 378 /* 379 ** check if in the list 380 ** if so, return a 1 and multiply it by the precision 381 ** if not, return a 0 and multiply it by the precision 382 */ 383 convert(smallint, 384 substring('0111111', 385 charindex(char(c.type), 386 @char_bin_types) + 1, 1)) * 387 /* calculate the precision */ 388 isnull(convert(int, c.prec), 389 isnull(convert(int, d.data_precision), 390 convert(int, c.length))) 391 + isnull(d.aux, convert(int, 392 ascii(substring('???AAAFFFCKFOLS', 393 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 394 ordinal_position = c.colid, 395 is_nullable = rtrim(substring('NO YES', 396 (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)) 397 398 FROM 399 syscolumns c, 400 sysobjects o, 401 sybsystemprocs.dbo.spt_datatype_info d, 402 systypes t 403 WHERE 404 o.name like @table_name 405 AND user_name(o.uid) like @table_owner 406 AND o.id = c.id 407 /* 408 ** We use syscolumn.usertype instead of syscolumn.type 409 ** to do join with systypes.usertype. This is because 410 ** for a column which allows null, type stores its 411 ** Server internal datatype whereas usertype still 412 ** stores its user defintion datatype. For an example, 413 ** a column of type 'decimal NULL', its usertype = 26, 414 ** representing decimal whereas its type = 106 415 ** representing decimaln. nullable in the select list 416 ** already tells user whether the column allows null. 417 ** In the case of user defining datatype, this makes 418 ** more sense for the user. 419 */ 420 AND c.usertype = t.usertype 421 AND t.type = d.ss_dtype 422 AND o.type != 'P' 423 AND c.name like @column_name 424 AND d.ss_dtype IN (111, 109, 38, 110, 43) /* Just *N types */ 425 AND c.usertype < 100 426 UNION 427 SELECT /* All other types including user data types */ 428 table_qualifier = DB_NAME(), 429 table_owner = USER_NAME(o.uid), 430 table_name = o.name, 431 column_name = c.name, 432 data_type = d.data_type + convert(smallint, 433 isnull(d.aux, 434 ascii(substring("666AAA@@@CB??GG", 435 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 436 - 60)), 437 type_name = rtrim(substring(isnull(stuff(d.type_name, 438 (c.status & 128) / 128, 439 char_length(d.type_name), 440 "numeric identity"), d.type_name), 441 1 + isnull(d.aux, 442 ascii(substring("III<<<MMMI<<A<A", 443 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 444 1)) - 60), 18)), 445 "precision" = isnull(convert(int, c.prec), 446 isnull(convert(int, d.data_precision), 447 convert(int, c.length))) 448 + isnull(d.aux, convert(int, 449 ascii(substring("???AAAFFFCKFOLS", 450 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 451 length = isnull(convert(int, c.length), 452 convert(int, d.length)) + 453 convert(int, isnull(d.aux, 454 ascii(substring("AAA<BB<DDDHJSPP", 455 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 456 1)) - 64)), 457 scale = isnull(convert(smallint, c.scale), 458 convert(smallint, d.numeric_scale)) + 459 convert(smallint, isnull(d.aux, 460 ascii(substring("<<<<<<<<<<<<<<?", 461 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 462 1)) - 60)), 463 radix = d.numeric_radix, 464 nullable = /* set nullability from status flag */ 465 convert(smallint, convert(bit, c.status & 8)), 466 remarks = convert(varchar(254), null), 467 ss_data_type = c.type, 468 colid = c.colid, 469 column_def = NULL, 470 sql_data_type = isnull(d.sql_data_type, 471 d.data_type + convert(smallint, 472 isnull(d.aux, 473 ascii(substring("666AAA@@@CB??GG", 474 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) 475 - 60))), 476 sql_datetime_sub = NULL, 477 /* 478 ** if the datatype is of type CHAR or BINARY 479 ** then set char_octet_length to the same value 480 ** assigned in the "prec" column. 481 ** 482 ** The first part of the logic is: 483 ** 484 ** if(c.type is in (47, 39, 45, 37, 35, 34)) 485 ** set char_octet_length = prec; 486 ** else 487 ** set char_octet_length = 0; 488 */ 489 char_octet_length = 490 /* 491 ** check if in the list 492 ** if so, return a 1 and multiply it by the precision 493 ** if not, return a 0 and multiply it by the precision 494 */ 495 convert(smallint, 496 substring('0111111', 497 charindex(char(c.type), 498 @char_bin_types) + 1, 1)) * 499 /* calculate the precision */ 500 isnull(convert(int, c.prec), 501 isnull(convert(int, d.data_precision), 502 convert(int, c.length))) 503 + isnull(d.aux, convert(int, 504 ascii(substring('???AAAFFFCKFOLS', 505 2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)), 506 ordinal_position = c.colid, 507 is_nullable = rtrim(substring('NO YES', 508 (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)) 509 FROM 510 syscolumns c, 511 sysobjects o, 512 sybsystemprocs.dbo.spt_datatype_info d, 513 systypes t 514 WHERE 515 o.name like @table_name 516 AND user_name(o.uid) like @table_owner 517 AND o.id = c.id 518 /* 519 ** We use syscolumn.usertype instead of syscolumn.type 520 ** to do join with systypes.usertype. This is because 521 ** for a column which allows null, type stores its 522 ** Server internal datatype whereas usertype still 523 ** stores its user defintion datatype. For an example, 524 ** a column of type 'decimal NULL', its usertype = 26, 525 ** representing decimal whereas its type = 106 526 ** representing decimaln. nullable in the select list 527 ** already tells user whether the column allows null. 528 ** In the case of user defining datatype, this makes 529 ** more sense for the user. 530 */ 531 AND c.usertype = t.usertype 532 /* 533 ** We need a equality join with 534 ** sybsystemprocs.dbo.spt_datatype_info here so that 535 ** there is only one qualified row returned from 536 ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding 537 ** duplicates. 538 */ 539 AND t.type = d.ss_dtype 540 AND c.name like @column_name 541 AND o.type != 'P' 542 AND c.name like @column_name 543 AND (d.ss_dtype NOT IN (111, 109, 38, 110, 43) /* No *N types */ 544 545 OR c.usertype >= 100) /* User defined types */ 546 547 ORDER BY table_owner, table_name, colid 548 end 549 550 return (0) 551
exec sp_procxmode 'sp_columns', 'AnyMode' go Grant Execute on sp_columns to public go
| RESULT SETS | |
| sp_columns_rset_002 | |
| sp_columns_rset_001 | |
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..syscolumns reads table sybsystemprocs..sysobjects reads table sybsystemprocs..spt_datatype_info reads table sybsystemprocs..systypes |
