DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_columns  14 déc. 14Defects Propagation Dependencies

1     
2     /* create a 1-off version of sp_jdbc_columns that has the additional
3     ** columns required for ODBC 2.0 and more columns required by
4     ** JDBC (from ODBC 3.0?).
5     */
6     
7     /* This is the version for servers which support UNION */
8     
9     CREATE OR REPLACE PROCEDURE sp_jdbc_columns(
10        @table_name varchar(771),
11        @table_owner varchar(32) = null,
12        @table_qualifier varchar(32) = null,
13        @column_name varchar(771) = null,
14        @version int = null /* Conform to JDBC 4.0 spec if @version is not null */
15    )
16    AS
17        /* Don't delete the following line. It is the checkpoint for sed */
18        /* Server dependent stored procedure add here ad ADDPOINT_COLS */
19    
20        declare @o_uid int
21        declare @o_name varchar(257)
22        declare @d_data_type smallint
23        declare @d_aux int
24        declare @d_ss_dtype tinyint
25        declare @d_type_name varchar(257)
26        declare @d_data_precision int
27        declare @d_numeric_scale smallint
28        declare @d_numeric_radix smallint
29        declare @d_sql_data_type smallint
30        declare @c_name varchar(257)
31        declare @c_length int
32        declare @c_prec tinyint
33        declare @c_scale tinyint
34        declare @c_type tinyint
35        declare @c_colid smallint
36        declare @c_status tinyint
37        declare @c_cdefault int
38        declare @xtname varchar(255)
39        declare @ident bit
40    
41        declare @msg varchar(250)
42        declare @full_table_name varchar(1542)
43        declare @table_id int
44        declare @char_bin_types varchar(30)
45        declare @uni_types varchar(10)
46        declare @column_default varchar(1024)
47        declare @startedInTransaction bit
48    
49    
50    
51    
52    
53        if (@@trancount = 0)
54        begin
55            set chained off
56        end
57    
58        if (@@trancount > 0)
59            select @startedInTransaction = 1
60        else
61            select @startedInTransaction = 0
62    
63        set transaction isolation level 1
64    
65        if (@startedInTransaction = 1)
66            save transaction jdbc_keep_temptables_from_tx
67    
68        /* this will make sure that all rows are sent even if
69        ** the client "set rowcount" is differect
70        */
71    
72        set rowcount 0
73    
74        /* character and binary datatypes */
75        select @char_bin_types =
76            char(47) + char(39) + char(45) + char(37) + char(35) + char(34)
77    
78        /* unichar, univarchar and unitext datatypes */
79        /* Note that the actual type numbers are 155 (unichar), 135 (univarchar)
80        and 174 (unitext), but because of issues that can arise when a server
81        has utf-8 as the default charset and a non-binary sort order, we need
82        to create a character string that is valid in utf-8. Therefore we
83        apply an offset of 60 to move the characters to be valid utf-8 chars.
84        The stored proc later does a similar calculation to utilize these
85        values properly*/
86        select @uni_types =
87            char(95) + char(75) + char(114)
88    
89        if @column_name is null select @column_name = '%'
90    
91        if @table_qualifier is not null
92        begin
93            if db_name() != @table_qualifier
94            begin /* 
95                ** If qualifier doesn't match current database: 18039
96                ** Table qualifier must be name of current database
97                */
98                exec sp_getmessage 18039, @msg output
99                raiserror 18039 @msg
100               return (1)
101           end
102       end
103   
104       if @table_name is null
105       begin /*	If table name not supplied, match all */
106           select @table_name = '%'
107       end
108   
109       if @table_owner is null
110       begin /* If unqualified table name */
111           SELECT @full_table_name = @table_name
112           select @table_owner = '%'
113       end
114       else
115       begin /* Qualified table name */
116           SELECT @full_table_name = @table_owner + '.' + @table_name
117       end
118   
119       /* create the temp table to hold our results */
120   
121       create table #jdbc_columns(
122           TABLE_CAT varchar(32) null,
123           TABLE_SCHEM varchar(32) null,
124           TABLE_NAME varchar(257) null,
125           COLUMN_NAME varchar(257) null,
126           DATA_TYPE smallint null,
127           TYPE_NAME varchar(255) null,
128           COLUMN_SIZE int null,
129           BUFFER_LENGTH int null,
130           DECIMAL_DIGITS int null,
131           NUM_PREC_RADIX int null,
132           NULLABLE int null,
133           REMARKS varchar(255) null,
134           COLUMN_DEF varchar(512) null,
135           SQL_DATA_TYPE int null,
136           SQL_DATETIME_SUB int null,
137           CHAR_OCTET_LENGTH int null,
138           ORDINAL_POSITION int null,
139           IS_NULLABLE varchar(10) null,
140           SCOPE_CATLOG varchar(32) null,
141           SCOPE_SCHEMA varchar(32) null,
142           SCOPE_TABLE varchar(32) null,
143           SOURCE_DATA_TYPE smallint null,
144           IS_AUTOINCREMENT varchar(10) null)
145   
146   
147   
148       /* Decide if we're going to take the branch where we are getting
149       information on one table (first branch), or more than one*/
150   
151       /* Get Object ID */
152       SELECT @table_id = object_id(@full_table_name)
153       /* If the table name parameter is valid, get the information */
154       if ((charindex('%', @full_table_name) = 0) and
155               (charindex('_', @full_table_name) = 0) and
156               (@table_id != 0))
157   
158       begin
159   
160           declare jdbc_columns_cursor1 cursor for
161           SELECT
162               c.cdefault,
163               c.colid,
164               c.length,
165               c.name,
166               c.prec,
167               c.scale,
168               c.status,
169               c.type,
170               d.aux,
171               d.data_precision,
172               d.data_type,
173               d.numeric_radix,
174               d.numeric_scale,
175               d.sql_data_type,
176               d.ss_dtype,
177               t.name,
178               o.name,
179               o.uid,
180               xtname,
181               convert(bit, (c.status & 0x80))
182   
183           FROM
184               syscolumns c,
185               sysobjects o,
186               sybsystemprocs.dbo.spt_jdbc_datatype_info d,
187               sysxtypes x,
188               systypes t
189           WHERE
190               o.id = @table_id
191               AND o.id = c.id
192               /*
193               ** We use syscolumn.usertype instead of syscolumn.type
194               ** to do join with systypes.usertype. This is because
195               ** for a column which allows null, type stores its
196               ** Server internal datatype whereas usertype still
197               ** stores its user defintion datatype.  For an example,
198               ** a column of type 'decimal NULL', its usertype = 26,
199               ** representing decimal whereas its type = 106
200               ** representing decimaln. nullable in the select list
201               ** already tells user whether the column allows null.
202               ** In the case of user defining datatype, this makes
203               ** more sense for the user.
204               */
205               AND c.usertype = t.usertype
206               AND t.type = d.ss_dtype
207               and c.xtype *= x.xtid
208               AND o.type != 'P'
209               AND c.name like @column_name ESCAPE '\'
210               AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */
211               AND c.usertype < 100
212   
213           plan '(join
214   	    						      ( scan (table ( o sysobjects)))
215   	    						      ( scan (table ( c syscolumns)))
216   	    						      ( scan (table ( x sysxtypes )))
217   	    						      ( scan (table ( t systypes )))
218   	    						      ( scan (table (d sybsystemprocs.dbo.spt_jdbc_datatype_info)))
219   		  )'
220   
221   
222   
223           open jdbc_columns_cursor1
224   
225           fetch jdbc_columns_cursor1 into
226               @c_cdefault,
227               @c_colid,
228               @c_length,
229               @c_name,
230               @c_prec,
231               @c_scale,
232               @c_status,
233               @c_type,
234               @d_aux,
235               @d_data_precision,
236               @d_data_type,
237               @d_numeric_radix,
238               @d_numeric_scale,
239               @d_sql_data_type,
240               @d_ss_dtype,
241               @d_type_name,
242               @o_name,
243               @o_uid,
244               @xtname,
245               @ident
246   
247           /* INTn, FLOATn, DATETIMEn and MONEYn types */
248   
249           while (@@sqlstatus = 0)
250           begin
251   
252               exec sp_drv_column_default @c_cdefault, @column_default out
253   
254               INSERT INTO #jdbc_columns values (
255                   /* TABLE_CAT */
256                   DB_NAME(),
257   
258                   /* TABLE_SCHEM */
259                   USER_NAME(@o_uid),
260   
261                   /* TABLE_NAME */
262                   @o_name,
263   
264                   /* COLUMN_NAME */
265                   @c_name,
266   
267                   /* DATA_TYPE */
268                   @d_data_type + convert(smallint,
269                   isnull(@d_aux,
270                       ascii(substring('666AAA@@@CB??GG',
271                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
272                       - 60)),
273   
274                   /* TYPE_NAME */
275                   case
276                       when @ident = 1 then
277                       case
278                           when @d_type_name = 'usmallint' then 'unsigned smallint identity'
279                           when @d_type_name = 'uint' then 'unsigned int identity'
280                           when @d_type_name = 'ubigint' then 'unsigned bigint identity'
281                           else
282                               isnull(@xtname, rtrim(substring(@d_type_name,
283                                           1 + isnull(@d_aux,
284                                               ascii(substring('III<<<MMMI<<A<A',
285                                                       2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
286                                                       1)) - 60), 255))) + ' identity'
287                       end
288                       when @d_type_name = 'usmallint' then 'unsigned smallint'
289                       when @d_type_name = 'uint' then 'unsigned int'
290                       when @d_type_name = 'ubigint' then 'unsigned bigint'
291                       else
292                           isnull(@xtname, rtrim(substring(@d_type_name,
293                                       1 + isnull(@d_aux,
294                                           ascii(substring('III<<<MMMI<<A<A',
295                                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
296                                                   1)) - 60), 255)))
297                   end,
298   
299                   /* COLUMN_SIZE */
300                   isnull(convert(int, @c_prec),
301                       isnull(convert(int, @d_data_precision),
302                           convert(int, @c_length)))
303                   + isnull(@d_aux, convert(int,
304                       ascii(substring('???AAAFFFCKFOLS',
305                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
306   
307                   /* BUFFER_LENGTH */
308                   isnull(convert(int, @c_prec),
309                       isnull(convert(int, @d_data_precision),
310                           convert(int, @c_length)))
311                   + isnull(@d_aux, convert(int,
312                       ascii(substring('???AAAFFFCKFOLS',
313                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
314   
315                   /* DECIMAL_DIGITS */
316                   isnull(convert(smallint, @c_scale),
317                       convert(smallint, @d_numeric_scale)) +
318                   convert(smallint, isnull(@d_aux,
319                       ascii(substring('<<<<<<<<<<<<<<?',
320                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
321                               1)) - 60)),
322   
323                   /* NUM_PREC_RADIX */
324                   @d_numeric_radix,
325   
326                   /* NULLABLE */
327                   /* set nullability from status flag */
328                   convert(smallint, convert(bit, @c_status & 8)),
329   
330                   /* REMARKS */
331                   convert(varchar(254), null), /* Remarks are NULL */
332   
333                   /* COLUMN_DEF */
334                   @column_default,
335   
336                   /* SQL_DATA_TYPE */
337                   isnull(@d_sql_data_type,
338                       @d_data_type + convert(smallint,
339                       isnull(@d_aux,
340                           ascii(substring('666AAA@@@CB??GG',
341                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
342                           - 60))),
343   
344                   /* SQL_DATATIME_SUB */
345                   NULL,
346   
347                   /* CHAR_OCTET_LENGTH */
348                   /*
349                   ** if the datatype is of type CHAR or BINARY
350                   ** then set char_octet_length to the same value
351                   ** assigned in the "prec" column.
352                   **
353                   ** The first part of the logic is:
354                   **
355                   **   if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34))
356                   **       set char_octet_length = prec;
357                   **   else
358                   **       set char_octet_length = 0;
359                   */
360                   /*
361                   ** check if in the list
362                   ** if so, return a 1 and multiply it by the precision 
363                   ** if not, return a 0 and multiply it by the precision
364                   */
365                   convert(smallint,
366                   substring('0111111',
367                       charindex(char(@c_type), @char_bin_types) +
368                       charindex(char(@c_type - 60), @uni_types) + 1, 1)) *
369                   /* calculate the precision */
370                   isnull(convert(int, @c_prec),
371                       isnull(convert(int, @d_data_precision),
372                           convert(int, @c_length)))
373                   + isnull(@d_aux, convert(int,
374                       ascii(substring('???AAAFFFCKFOLS',
375                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
376   
377                   /* ORDINAL_POSITION */
378                   @c_colid,
379   
380                   /* IS_NULLABLE */
381                   rtrim(substring('NO YES', convert(smallint,
382                           convert(bit, @c_status & 8) * 3) + 1, 3)),
383                   null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 
384                   REF data type not supported*/
385                   /* IS_AUTOINCREMENT */
386                   rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3))
387   
388               ) /* close paren for values (*) */
389   
390               fetch jdbc_columns_cursor1 into
391                   @c_cdefault,
392                   @c_colid,
393                   @c_length,
394                   @c_name,
395                   @c_prec,
396                   @c_scale,
397                   @c_status,
398                   @c_type,
399                   @d_aux,
400                   @d_data_precision,
401                   @d_data_type,
402                   @d_numeric_radix,
403                   @d_numeric_scale,
404                   @d_sql_data_type,
405                   @d_ss_dtype,
406                   @d_type_name,
407                   @o_name,
408                   @o_uid,
409                   @xtname,
410                   @ident
411   
412           end
413   
414           deallocate cursor jdbc_columns_cursor1
415   
416           declare jdbc_columns_cursor2 cursor for
417           SELECT
418               c.cdefault,
419               c.colid,
420               c.length,
421               c.name,
422               c.prec,
423               c.scale,
424               c.status,
425               c.type,
426               d.aux,
427               d.data_precision,
428               d.data_type,
429               d.numeric_radix,
430               d.numeric_scale,
431               d.sql_data_type,
432               d.ss_dtype,
433               t.name,
434               o.name,
435               o.uid,
436               xtname,
437               convert(bit, (c.status & 0x80))
438           FROM
439               syscolumns c,
440               sysobjects o,
441               sybsystemprocs.dbo.spt_jdbc_datatype_info d,
442               sysxtypes x,
443               systypes t
444           WHERE
445               o.id = @table_id
446               AND o.id = c.id
447               /*
448               ** We use syscolumn.usertype instead of syscolumn.type
449               ** to do join with systypes.usertype. This is because
450               ** for a column which allows null, type stores its
451               ** Server internal datatype whereas usertype still
452               ** stores its user defintion datatype.  For an example,
453               ** a column of type 'decimal NULL', its usertype = 26,
454               ** representing decimal whereas its type = 106 
455               ** representing decimaln. nullable in the select list
456               ** already tells user whether the column allows null.
457               ** In the case of user defining datatype, this makes
458               ** more sense for the user.
459               */
460               AND c.usertype = t.usertype
461               /*
462               ** We need a equality join with 
463               ** sybsystemprocs.dbo.spt_jdbc_datatype_info here so that
464               ** there is only one qualified row returned from 
465               ** sybsystemprocs.dbo.spt_jdbc_datatype_info, thus avoiding
466               ** duplicates.
467               */
468               AND t.type = d.ss_dtype
469               and c.xtype *= x.xtid
470               AND o.type != 'P'
471               AND c.name like @column_name ESCAPE '\'
472               AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */
473                   OR c.usertype >= 100) /* User defined types */
474   
475           plan '(join
476   								      ( scan (table ( o sysobjects)))
477   								      ( scan (table ( c syscolumns)))
478   								      ( scan (table ( x sysxtypes )))
479   								      ( scan (table ( d sybsystemprocs.dbo.spt_jdbc_datatype_info)))
480   								      ( scan (table ( t systypes )))
481   								      
482   		   )'
483   
484   
485           open jdbc_columns_cursor2
486   
487           fetch jdbc_columns_cursor2 into
488               @c_cdefault,
489               @c_colid,
490               @c_length,
491               @c_name,
492               @c_prec,
493               @c_scale,
494               @c_status,
495               @c_type,
496               @d_aux,
497               @d_data_precision,
498               @d_data_type,
499               @d_numeric_radix,
500               @d_numeric_scale,
501               @d_sql_data_type,
502               @d_ss_dtype,
503               @d_type_name,
504               @o_name,
505               @o_uid,
506               @xtname,
507               @ident
508   
509           while (@@sqlstatus = 0)
510           begin
511   
512               exec sp_drv_column_default @c_cdefault, @column_default out
513   
514               /* All other types including user data types */
515   
516               INSERT INTO #jdbc_columns values (
517   
518                   /* TABLE_CAT */
519                   DB_NAME(),
520   
521                   /* TABLE_SCHEM */
522                   USER_NAME(@o_uid),
523   
524                   /* TABLE_NAME */
525                   @o_name,
526   
527                   /*COLUMN_NAME*/
528                   @c_name,
529   
530                   /* DATA_TYPE */
531                   @d_data_type + convert(smallint,
532                   isnull(@d_aux,
533                       ascii(substring('666AAA@@@CB??GG',
534                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
535                       - 60)),
536   
537                   /* TYPE_NAME */
538                   case
539                       when @ident = 1 then
540                       case
541                           when @d_type_name = 'usmallint' then 'unsigned smallint identity'
542                           when @d_type_name = 'uint' then 'unsigned int identity'
543                           when @d_type_name = 'ubigint' then 'unsigned bigint identity'
544                           else
545                               isnull(@xtname, rtrim(substring(@d_type_name,
546                                           1 + isnull(@d_aux,
547                                               ascii(substring('III<<<MMMI<<A<A',
548                                                       2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
549                                                       1)) - 60), 255))) + ' identity'
550                       end
551                       when @d_type_name = 'usmallint' then 'unsigned smallint'
552                       when @d_type_name = 'uint' then 'unsigned int'
553                       when @d_type_name = 'ubigint' then 'unsigned bigint'
554                       else
555                           isnull(@xtname, rtrim(substring(@d_type_name,
556                                       1 + isnull(@d_aux,
557                                           ascii(substring('III<<<MMMI<<A<A',
558                                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
559                                                   1)) - 60), 255)))
560                   end,
561   
562                   /* COLUMN_SIZE */
563                   isnull(convert(int, @c_prec),
564                       isnull(convert(int, @d_data_precision),
565                           convert(int, @c_length / (1 +
566                           (convert(smallint,
567                           substring('011',
568                               charindex(char(@c_type - 60), @uni_types) + 1, 1)))))))
569                   + isnull(@d_aux, convert(int,
570                       ascii(substring('???AAAFFFCKFOLS',
571                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
572   
573                   /* BUFFER_LENGTH */
574                   isnull(convert(int, @c_prec),
575                       isnull(convert(int, @d_data_precision),
576                           convert(int, @c_length)))
577                   + isnull(@d_aux, convert(int,
578                       ascii(substring('???AAAFFFCKFOLS',
579                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
580   
581                   /* DECIMAL_DIGITS */
582                   isnull(convert(smallint, @c_scale),
583                       convert(smallint, @d_numeric_scale)) +
584                   convert(smallint, isnull(@d_aux,
585                       ascii(substring('<<<<<<<<<<<<<<?',
586                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
587                               1)) - 60)),
588   
589                   /* NUM_PREC_RADIX */
590                   @d_numeric_radix,
591   
592                   /* NULLABLE */
593                   convert(smallint, convert(bit, @c_status & 8)),
594   
595                   /* REMARKS */
596                   convert(varchar(254), null),
597   
598                   /* COLUMN_DEF */
599                   @column_default,
600   
601                   /* SQL_DATA_TYPE */
602                   isnull(@d_sql_data_type,
603                       @d_data_type + convert(smallint,
604                       isnull(@d_aux,
605                           ascii(substring('666AAA@@@CB??GG',
606                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
607                           - 60))),
608   
609                   /* SQL_DATETIME_SUB */
610                   NULL,
611   
612                   /* CHAR_OCTET_LENGTH */
613                   /*
614                   ** if the datatype is of type CHAR or BINARY
615                   ** then set char_octet_length to the same value
616                   ** assigned in the "prec" column.
617                   **
618                   ** The first part of the logic is:
619                   **
620                   **   if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34))
621                   **       set char_octet_length = prec;
622                   **   else
623                   **       set char_octet_length = 0;
624                   */
625   
626                   /*
627                   ** check if in the list
628                   ** if so, return a 1 and multiply it by the precision 
629                   ** if not, return a 0 and multiply it by the precision
630                   */
631                   convert(smallint, substring('0111111',
632                       charindex(char(@c_type), @char_bin_types) +
633                       charindex(char(@c_type - 60), @uni_types) + 1, 1)) *
634                   /* calculate the precision */
635                   isnull(convert(int, @c_prec),
636                       isnull(convert(int, @d_data_precision),
637                           convert(int, @c_length)))
638                   + isnull(@d_aux, convert(int,
639                       ascii(substring('???AAAFFFCKFOLS',
640                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
641   
642                   /* ORDINAL_POSITION */
643                   @c_colid,
644   
645                   /* IS_NULLABLE */
646                   rtrim(substring('NO YES', convert(smallint,
647                           convert(bit, @c_status & 8) * 3) + 1, 3)),
648                   null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 
649                   REF data type not supported*/
650                   /* IS_AUTOINCREMENT */
651                   rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3))
652   
653   
654               ) /* close paren for values (*) */
655   
656               fetch jdbc_columns_cursor2 into
657                   @c_cdefault,
658                   @c_colid,
659                   @c_length,
660                   @c_name,
661                   @c_prec,
662                   @c_scale,
663                   @c_status,
664                   @c_type,
665                   @d_aux,
666                   @d_data_precision,
667                   @d_data_type,
668                   @d_numeric_radix,
669                   @d_numeric_scale,
670                   @d_sql_data_type,
671                   @d_ss_dtype,
672                   @d_type_name,
673                   @o_name,
674                   @o_uid,
675                   @xtname,
676                   @ident
677   
678           end /* while loop */
679   
680           deallocate cursor jdbc_columns_cursor2
681   
682       end /* if we have just one table */
683   
684       else
685       begin
686   
687           /* We'll be iterating over more than one table */
688   
689           declare jdbc_columns_cursor3 cursor for
690           select
691               c.cdefault,
692               c.colid,
693               c.length,
694               c.name,
695               c.prec,
696               c.scale,
697               c.status,
698               c.type,
699               d.aux,
700               d.data_precision,
701               d.data_type,
702               d.numeric_radix,
703               d.numeric_scale,
704               d.sql_data_type,
705               d.ss_dtype,
706               t.name,
707               o.name,
708               o.uid,
709               xtname,
710               convert(bit, (c.status & 0x80))
711   
712           FROM
713               syscolumns c,
714               sysobjects o,
715               sybsystemprocs.dbo.spt_jdbc_datatype_info d,
716               sysxtypes x,
717               systypes t
718   
719           WHERE
720               o.name like @table_name ESCAPE '\'
721               AND user_name(o.uid) like @table_owner ESCAPE '\'
722               AND o.id = c.id
723               /*
724               ** We use syscolumn.usertype instead of syscolumn.type
725               ** to do join with systypes.usertype. This is because
726               ** for a column which allows null, type stores its
727               ** Server internal datatype whereas usertype still
728               ** stores its user defintion datatype.  For an example,
729               ** a column of type 'decimal NULL', its usertype = 26,
730               ** representing decimal whereas its type = 106
731               ** representing decimaln. nullable in the select list
732               ** already tells user whether the column allows null.
733               ** In the case of user defining datatype, this makes
734               ** more sense for the user.
735               */
736               AND c.usertype = t.usertype
737               AND t.type = d.ss_dtype
738               AND o.type != 'P'
739               and c.xtype *= x.xtid
740               AND c.name like @column_name ESCAPE '\'
741               AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */
742               AND c.usertype < 100
743   
744           plan '(join
745   	    						      ( scan (table ( o sysobjects)))
746   	    						      ( scan (table ( c syscolumns)))
747   	    						      ( scan (table ( t systypes )))
748   	    						      ( scan (table ( d sybsystemprocs.dbo.spt_jdbc_datatype_info)))
749   	    						      ( scan (table ( x sysxtypes )))
750   	    						      
751   	    						      
752   	    						      
753   	            )'
754   
755           open jdbc_columns_cursor3
756   
757           fetch jdbc_columns_cursor3 into
758               @c_cdefault,
759               @c_colid,
760               @c_length,
761               @c_name,
762               @c_prec,
763               @c_scale,
764               @c_status,
765               @c_type,
766               @d_aux,
767               @d_data_precision,
768               @d_data_type,
769               @d_numeric_radix,
770               @d_numeric_scale,
771               @d_sql_data_type,
772               @d_ss_dtype,
773               @d_type_name,
774               @o_name,
775               @o_uid,
776               @xtname,
777               @ident
778   
779   
780           /* INTn, FLOATn, DATETIMEn and MONEYn types */
781   
782           while (@@sqlstatus = 0)
783           begin
784   
785               exec sp_drv_column_default @c_cdefault, @column_default out
786   
787               INSERT INTO #jdbc_columns values (
788                   /* TABLE_CAT */
789                   DB_NAME(),
790   
791                   /* TABLE_SCHEM */
792                   USER_NAME(@o_uid),
793   
794                   /* TABLE_NAME */
795                   @o_name,
796   
797                   /* COLUMN_NAME */
798                   @c_name,
799   
800                   /* DATA_TYPE */
801                   @d_data_type + convert(smallint,
802                   isnull(@d_aux,
803                       ascii(substring('666AAA@@@CB??GG',
804                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
805                       - 60)),
806   
807                   /* TYPE_NAME */
808                   case
809                       when @ident = 1 then
810                       case
811                           when @d_type_name = 'usmallint' then 'unsigned smallint identity'
812                           when @d_type_name = 'uint' then 'unsigned int identity'
813                           when @d_type_name = 'ubigint' then 'unsigned bigint identity'
814                           else
815                               isnull(@xtname, rtrim(substring(@d_type_name,
816                                           1 + isnull(@d_aux,
817                                               ascii(substring('III<<<MMMI<<A<A',
818                                                       2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
819                                                       1)) - 60), 255))) + ' identity'
820                       end
821                       when @d_type_name = 'usmallint' then 'unsigned smallint'
822                       when @d_type_name = 'uint' then 'unsigned int'
823                       when @d_type_name = 'ubigint' then 'unsigned bigint'
824                       else
825                           isnull(@xtname, rtrim(substring(@d_type_name,
826                                       1 + isnull(@d_aux,
827                                           ascii(substring('III<<<MMMI<<A<A',
828                                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
829                                                   1)) - 60), 255)))
830                   end,
831   
832                   /* COLUMN_SIZE */
833                   isnull(convert(int, @c_prec),
834                       isnull(convert(int, @d_data_precision),
835                           convert(int, @c_length)))
836                   + isnull(@d_aux, convert(int,
837                       ascii(substring('???AAAFFFCKFOLS',
838                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
839   
840                   /* BUFFER_LENGTH */
841                   isnull(convert(int, @c_prec),
842                       isnull(convert(int, @d_data_precision),
843                           convert(int, @c_length)))
844                   + isnull(@d_aux, convert(int,
845                       ascii(substring('???AAAFFFCKFOLS',
846                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
847   
848                   /* DECIMAL_DIGITS */
849                   isnull(convert(smallint, @c_scale),
850                       convert(smallint, @d_numeric_scale)) +
851                   convert(smallint, isnull(@d_aux,
852                       ascii(substring('<<<<<<<<<<<<<<?',
853                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
854                               1)) - 60)),
855   
856                   /* NUM_PREC_RADIX */
857                   @d_numeric_radix,
858   
859                   /* NULLABLE */
860                   /* set nullability from status flag */
861                   convert(smallint, convert(bit, @c_status & 8)),
862   
863                   /* REMARKS */
864                   convert(varchar(254), null), /* Remarks are NULL */
865   
866                   /* COLUMN_DEF */
867                   @column_default,
868   
869                   /* SQL_DATA_TYPE */
870                   isnull(@d_sql_data_type,
871                       @d_data_type + convert(smallint,
872                       isnull(@d_aux,
873                           ascii(substring('666AAA@@@CB??GG',
874                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
875                           - 60))),
876   
877                   /* SQL_DATATIME_SUB */
878                   NULL,
879   
880                   /* CHAR_OCTET_LENGTH */
881                   /*
882                   ** if the datatype is of type CHAR or BINARY
883                   ** then set char_octet_length to the same value
884                   ** assigned in the "prec" column.
885                   **
886                   ** The first part of the logic is:
887                   **
888                   **   if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34))
889                   **       set char_octet_length = prec;
890                   **   else
891                   **       set char_octet_length = 0;
892                   */
893   
894                   /*
895                   ** check if in the list
896                   ** if so, return a 1 and multiply it by the precision 
897                   ** if not, return a 0 and multiply it by the precision
898                   */
899                   convert(smallint,
900                   substring('0111111',
901                       charindex(char(@c_type), @char_bin_types) +
902                       charindex(char(@c_type - 60), @uni_types) + 1, 1)) *
903                   /* calculate the precision */
904                   isnull(convert(int, @c_prec),
905                       isnull(convert(int, @d_data_precision),
906                           convert(int, @c_length)))
907                   + isnull(@d_aux, convert(int,
908                       ascii(substring('???AAAFFFCKFOLS',
909                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
910   
911                   /* ORDINAL_POSITION */
912                   @c_colid,
913   
914                   /* IS_NULLABLE */
915                   rtrim(substring('NO YES', convert(smallint,
916                           convert(bit, @c_status & 8) * 3) + 1, 3)),
917                   null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 
918                   REF data type not supported*/
919                   /* IS_AUTOINCREMENT */
920                   rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3))
921   
922               ) /* right paren for values (*) */
923   
924               fetch jdbc_columns_cursor3 into
925                   @c_cdefault,
926                   @c_colid,
927                   @c_length,
928                   @c_name,
929                   @c_prec,
930                   @c_scale,
931                   @c_status,
932                   @c_type,
933                   @d_aux,
934                   @d_data_precision,
935                   @d_data_type,
936                   @d_numeric_radix,
937                   @d_numeric_scale,
938                   @d_sql_data_type,
939                   @d_ss_dtype,
940                   @d_type_name,
941                   @o_name,
942                   @o_uid,
943                   @xtname,
944                   @ident
945   
946           end /* while loop */
947   
948           deallocate cursor jdbc_columns_cursor3
949   
950           declare jdbc_columns_cursor4 cursor for
951           SELECT
952               c.cdefault,
953               c.colid,
954               c.length,
955               c.name,
956               c.prec,
957               c.scale,
958               c.status,
959               c.type,
960               d.aux,
961               d.data_precision,
962               d.data_type,
963               d.numeric_radix,
964               d.numeric_scale,
965               d.sql_data_type,
966               d.ss_dtype,
967               t.name,
968               o.name,
969               o.uid,
970               xtname,
971               convert(bit, (c.status & 0x80))
972           FROM
973               syscolumns c,
974               sysobjects o,
975               sybsystemprocs.dbo.spt_jdbc_datatype_info d,
976               sysxtypes x,
977               systypes t
978           WHERE
979               o.name like @table_name ESCAPE '\'
980               AND user_name(o.uid) like @table_owner ESCAPE '\'
981               and c.xtype *= x.xtid
982               AND o.id = c.id
983               /*
984               ** We use syscolumn.usertype instead of syscolumn.type
985               ** to do join with systypes.usertype. This is because
986               ** for a column which allows null, type stores its
987               ** Server internal datatype whereas usertype still
988               ** stores its user defintion datatype.  For an example,
989               ** a column of type 'decimal NULL', its usertype = 26,
990               ** representing decimal whereas its type = 106 
991               ** representing decimaln. nullable in the select list
992               ** already tells user whether the column allows null.
993               ** In the case of user defining datatype, this makes
994               ** more sense for the user.
995               */
996               AND c.usertype = t.usertype
997               /*
998               ** We need a equality join with 
999               ** sybsystemprocs.dbo.spt_jdbc_datatype_info here so that
1000              ** there is only one qualified row returned from 
1001              ** sybsystemprocs.dbo.spt_jdbc_datatype_info, thus avoiding
1002              ** duplicates.
1003              */
1004              AND t.type = d.ss_dtype
1005              AND o.type != 'P'
1006              AND c.name like @column_name ESCAPE '\'
1007              AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */
1008                  OR c.usertype >= 100) /* User defined types */
1009  
1010          plan '(join
1011  								      ( scan (table ( o sysobjects)))
1012  								      ( scan (table ( c syscolumns)))
1013  								      ( scan (table ( t systypes )))
1014  								      ( scan (table ( d sybsystemprocs.dbo.spt_jdbc_datatype_info)))
1015  								      ( scan (table ( x sysxtypes )))
1016  								      
1017  								      
1018  								      
1019  			)'
1020  
1021          open jdbc_columns_cursor4
1022  
1023          fetch jdbc_columns_cursor4 into
1024              @c_cdefault,
1025              @c_colid,
1026              @c_length,
1027              @c_name,
1028              @c_prec,
1029              @c_scale,
1030              @c_status,
1031              @c_type,
1032              @d_aux,
1033              @d_data_precision,
1034              @d_data_type,
1035              @d_numeric_radix,
1036              @d_numeric_scale,
1037              @d_sql_data_type,
1038              @d_ss_dtype,
1039              @d_type_name,
1040              @o_name,
1041              @o_uid,
1042              @xtname,
1043              @ident
1044  
1045  
1046          while (@@sqlstatus = 0)
1047          begin
1048              exec sp_drv_column_default @c_cdefault, @column_default out
1049  
1050              /* All other types including user data types */
1051              INSERT INTO #jdbc_columns values (
1052  
1053                  /* TABLE_CAT */
1054                  DB_NAME(),
1055  
1056                  /* TABLE_SCHEM */
1057                  USER_NAME(@o_uid),
1058  
1059                  /* TABLE_NAME */
1060                  @o_name,
1061  
1062                  /*COLUMN_NAME*/
1063                  @c_name,
1064  
1065                  /* DATA_TYPE */
1066                  @d_data_type + convert(smallint,
1067                  isnull(@d_aux,
1068                      ascii(substring('666AAA@@@CB??GG',
1069                              2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
1070                      - 60)),
1071  
1072                  /* TYPE_NAME */
1073                  case
1074                      when @ident = 1 then
1075                      case
1076                          when @d_type_name = 'usmallint' then 'unsigned smallint identity'
1077                          when @d_type_name = 'uint' then 'unsigned int identity'
1078                          when @d_type_name = 'ubigint' then 'unsigned bigint identity'
1079                          else
1080                              isnull(@xtname, rtrim(substring(@d_type_name,
1081                                          1 + isnull(@d_aux,
1082                                              ascii(substring('III<<<MMMI<<A<A',
1083                                                      2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
1084                                                      1)) - 60), 255))) + ' identity'
1085                      end
1086                      when @d_type_name = 'usmallint' then 'unsigned smallint'
1087                      when @d_type_name = 'uint' then 'unsigned int'
1088                      when @d_type_name = 'ubigint' then 'unsigned bigint'
1089                      else
1090                          isnull(@xtname, rtrim(substring(@d_type_name,
1091                                      1 + isnull(@d_aux,
1092                                          ascii(substring('III<<<MMMI<<A<A',
1093                                                  2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
1094                                                  1)) - 60), 255)))
1095                  end,
1096  
1097                  /* COLUMN_SIZE */
1098                  isnull(convert(int, @c_prec),
1099                      isnull(convert(int, @d_data_precision),
1100                          convert(int, @c_length / (1 +
1101                          (convert(smallint,
1102                          substring('011',
1103                              charindex(char(@c_type - 60), @uni_types) + 1, 1)))))))
1104                  + isnull(@d_aux, convert(int,
1105                      ascii(substring('???AAAFFFCKFOLS',
1106                              2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
1107  
1108                  /* BUFFER_LENGTH */
1109                  isnull(convert(int, @c_prec),
1110                      isnull(convert(int, @d_data_precision),
1111                          convert(int, @c_length)))
1112                  + isnull(@d_aux, convert(int,
1113                      ascii(substring('???AAAFFFCKFOLS',
1114                              2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
1115  
1116                  /* DECIMAL_DIGITS */
1117                  isnull(convert(smallint, @c_scale),
1118                      convert(smallint, @d_numeric_scale)) +
1119                  convert(smallint, isnull(@d_aux,
1120                      ascii(substring('<<<<<<<<<<<<<<?',
1121                              2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
1122                              1)) - 60)),
1123  
1124                  /* NUM_PREC_RADIX */
1125                  @d_numeric_radix,
1126  
1127                  /* NULLABLE */
1128                  convert(smallint, convert(bit, @c_status & 8)),
1129  
1130                  /* REMARKS */
1131                  convert(varchar(254), null),
1132  
1133                  /* COLUMN_DEF */
1134                  @column_default,
1135  
1136                  /* SQL_DATA_TYPE */
1137                  isnull(@d_sql_data_type,
1138                      @d_data_type + convert(smallint,
1139                      isnull(@d_aux,
1140                          ascii(substring('666AAA@@@CB??GG',
1141                                  2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
1142                          - 60))),
1143  
1144                  /* SQL_DATETIME_SUB */
1145                  NULL,
1146  
1147                  /* CHAR_OCTET_LENGTH */
1148                  /*
1149                  ** if the datatype is of type CHAR or BINARY
1150                  ** then set char_octet_length to the same value
1151                  ** assigned in the "prec" column.
1152                  **
1153                  ** The first part of the logic is:
1154                  **
1155                  **   if(c.type is in (155, 135, 47, 39, 45, 37, 35, 34))
1156                  **       set char_octet_length = prec;
1157                  **   else
1158                  **       set char_octet_length = 0;
1159                  */
1160  
1161                  /*
1162                  ** check if in the list
1163                  ** if so, return a 1 and multiply it by the precision 
1164                  ** if not, return a 0 and multiply it by the precision
1165                  */
1166                  convert(smallint, substring('0111111',
1167                      charindex(char(@c_type), @char_bin_types) +
1168                      charindex(char(@c_type - 60), @uni_types) + 1, 1)) *
1169                  /* calculate the precision */
1170                  isnull(convert(int, @c_prec),
1171                      isnull(convert(int, @d_data_precision),
1172                          convert(int, @c_length)))
1173                  + isnull(@d_aux, convert(int,
1174                      ascii(substring('???AAAFFFCKFOLS',
1175                              2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
1176  
1177                  /* ORDINAL_POSITION */
1178                  @c_colid,
1179  
1180                  /* IS_NULLABLE */
1181                  rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 8) * 3) + 1, 3)),
1182                  null, null, null, null, /*SCOPE_CATLOG, SCOPE_SCHEMA , SCOPE_TABLE , SOURCE_DATA_TYPE 
1183                  REF data type not supported*/
1184                  /* IS_AUTOINCREMENT */
1185                  rtrim(substring('NO YES', convert(smallint, convert(bit, @c_status & 128) * 3) + 1, 3))
1186  
1187              ) /* close paren for values (*) */
1188  
1189              fetch jdbc_columns_cursor4 into
1190                  @c_cdefault,
1191                  @c_colid,
1192                  @c_length,
1193                  @c_name,
1194                  @c_prec,
1195                  @c_scale,
1196                  @c_status,
1197                  @c_type,
1198                  @d_aux,
1199                  @d_data_precision,
1200                  @d_data_type,
1201                  @d_numeric_radix,
1202                  @d_numeric_scale,
1203                  @d_sql_data_type,
1204                  @d_ss_dtype,
1205                  @d_type_name,
1206                  @o_name,
1207                  @o_uid,
1208                  @xtname,
1209                  @ident
1210  
1211          end /* while loop */
1212  
1213          deallocate cursor jdbc_columns_cursor4
1214  
1215      end
1216      if @version is not null
1217      begin
1218          /* Adaptive Server has expanded all '*' elements in the following statement */ SELECT #jdbc_columns.TABLE_CAT, #jdbc_columns.TABLE_SCHEM, #jdbc_columns.TABLE_NAME, #jdbc_columns.COLUMN_NAME, #jdbc_columns.DATA_TYPE, #jdbc_columns.TYPE_NAME, #jdbc_columns.COLUMN_SIZE, #jdbc_columns.BUFFER_LENGTH, #jdbc_columns.DECIMAL_DIGITS, #jdbc_columns.NUM_PREC_RADIX, #jdbc_columns.NULLABLE, #jdbc_columns.REMARKS, #jdbc_columns.COLUMN_DEF, #jdbc_columns.SQL_DATA_TYPE, #jdbc_columns.SQL_DATETIME_SUB, #jdbc_columns.CHAR_OCTET_LENGTH, #jdbc_columns.ORDINAL_POSITION, #jdbc_columns.IS_NULLABLE, #jdbc_columns.SCOPE_CATLOG, #jdbc_columns.SCOPE_SCHEMA, #jdbc_columns.SCOPE_TABLE, #jdbc_columns.SOURCE_DATA_TYPE, #jdbc_columns.IS_AUTOINCREMENT FROM #jdbc_columns
1219          ORDER BY TABLE_CAT, TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION
1220      end
1221      else
1222          SELECT TABLE_CAT
1223              , TABLE_SCHEM
1224              , TABLE_NAME
1225              , COLUMN_NAME
1226              , DATA_TYPE
1227              , TYPE_NAME
1228              , COLUMN_SIZE
1229              , BUFFER_LENGTH
1230              , DECIMAL_DIGITS
1231              , NUM_PREC_RADIX
1232              , NULLABLE
1233              , REMARKS
1234              , COLUMN_DEF
1235              , SQL_DATA_TYPE
1236              , SQL_DATETIME_SUB
1237              , CHAR_OCTET_LENGTH
1238              , ORDINAL_POSITION
1239              , IS_NULLABLE
1240              , SCOPE_CATLOG
1241              , SCOPE_SCHEMA
1242              , SCOPE_TABLE
1243              , SOURCE_DATA_TYPE
1244          FROM #jdbc_columns
1245          ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION
1246  
1247      drop table #jdbc_columns
1248  
1249      if (@startedInTransaction = 1)
1250          rollback transaction jdbc_keep_temptables_from_tx
1251  
1252      return (0)
1253  
1254  
1255  


exec sp_procxmode 'sp_jdbc_columns', 'AnyMode'
go

Grant Execute on sp_jdbc_columns to public
go
RESULT SETS
sp_jdbc_columns_rset_002
sp_jdbc_columns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 206
 QJWI 5 Join or Sarg Without Index 207
 QJWI 5 Join or Sarg Without Index 468
 QJWI 5 Join or Sarg Without Index 469
 QJWI 5 Join or Sarg Without Index 737
 QJWI 5 Join or Sarg Without Index 739
 QJWI 5 Join or Sarg Without Index 981
 QJWI 5 Join or Sarg Without Index 1004
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysxtypes sybsystemprocs..sysxtypes
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 334
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 599
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 867
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 1134
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 211
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 473
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 742
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 1008
 TNOI 4 Table with no index sybsystemprocs..spt_jdbc_datatype_info sybsystemprocs..spt_jdbc_datatype_info
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_columns_cursor1 161
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_columns_cursor2 417
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_columns_cursor3 690
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause jdbc_columns_cursor4 951
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 190
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 191
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 191
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 445
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 446
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 446
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 722
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 722
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 982
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 982
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 181
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 437
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 710
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 971
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_columns  
 MGTP 3 Grant to public sybsystemprocs..spt_jdbc_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MGTP 3 Grant to public sybsystemprocs..sysxtypes  
 MNER 3 No Error Check should check return value of exec 98
 MNER 3 No Error Check should check return value of exec 252
 MNER 3 No Error Check should check @@error after insert 254
 MNER 3 No Error Check should check return value of exec 512
 MNER 3 No Error Check should check @@error after insert 516
 MNER 3 No Error Check should check return value of exec 785
 MNER 3 No Error Check should check @@error after insert 787
 MNER 3 No Error Check should check return value of exec 1048
 MNER 3 No Error Check should check @@error after insert 1051
 MUCO 3 Useless Code Useless Brackets in create proc 9
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 100
 MUCO 3 Useless Code Useless Brackets 154
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 509
 MUCO 3 Useless Code Useless Brackets 566
 MUCO 3 Useless Code Useless Brackets 782
 MUCO 3 Useless Code Useless Brackets 1046
 MUCO 3 Useless Code Useless Brackets 1101
 MUCO 3 Useless Code Useless Brackets 1249
 MUCO 3 Useless Code Useless Brackets 1252
 QCRS 3 Conditional Result Set 1218
 QCRS 3 Conditional Result Set 1222
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan...'(join ( scan (table ( o sysobjects))) ( scan (table ( c syscolumns))) ( scan (table ( x sysxtypes ))) ( scan (table ( t systypes ))) ( scan (table (d sybsystemprocs.dbo.spt_jdbc_datatype_info))) )' 161
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan...'(join ( scan (table ( o sysobjects))) ( scan (table ( c syscolumns))) ( scan (table ( x sysxtypes ))) ( scan (table ( d sybsystemprocs.dbo.spt_jdbc_datatype_info))) ( scan (table ( t systypes ))) )' 417
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan...'(join ( scan (table ( o sysobjects))) ( scan (table ( c syscolumns))) ( scan (table ( t systypes ))) ( scan (table ( d sybsystemprocs.dbo.spt_jdbc_datatype_info))) ( scan (table ( x sysxtypes ))) )' 690
 QFAP 3 Force abstract plan ABSTRACT_PLAN_CLAUSE plan...'(join ( scan (table ( o sysobjects))) ( scan (table ( c syscolumns))) ( scan (table ( t systypes ))) ( scan (table ( d sybsystemprocs.dbo.spt_jdbc_datatype_info))) ( scan (table ( x sysxtypes ))) )' 951
 QISO 3 Set isolation level 63
 QNAO 3 Not using ANSI Outer Join 183
 QNAO 3 Not using ANSI Outer Join 438
 QNAO 3 Not using ANSI Outer Join 712
 QNAO 3 Not using ANSI Outer Join 972
 QNUA 3 Should use Alias: Column xtname should use alias x 180
 QNUA 3 Should use Alias: Column xtname should use alias x 436
 QNUA 3 Should use Alias: Column xtname should use alias x 709
 QNUA 3 Should use Alias: Column xtname should use alias x 970
 CUPD 2 Updatable Cursor Marker (updatable by default) 161
 CUPD 2 Updatable Cursor Marker (updatable by default) 417
 CUPD 2 Updatable Cursor Marker (updatable by default) 690
 CUPD 2 Updatable Cursor Marker (updatable by default) 951
 MRST 2 Result Set Marker 1218
 MRST 2 Result Set Marker 1222
 MTR1 2 Metrics: Comments Ratio Comments: 22% 9
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 52 = 52dec - 2exi + 2 9
 MTR3 2 Metrics: Query Complexity Complexity: 204 9

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscolumns  
writes table sybsystemprocs..sp_jdbc_columns_rset_002 
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
reads table sybsystemprocs..sysxtypes  
read_writes table tempdb..#jdbc_columns (1) 
reads table sybsystemprocs..spt_jdbc_datatype_info  
calls proc sybsystemprocs..sp_drv_column_default  
   reads table sybsystemprocs..syscomments  
writes table sybsystemprocs..sp_jdbc_columns_rset_001 
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..sysobjects