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