DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_columns  31 Aug 14Defects 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_odbc_columns(
20        @table_name varchar(771),
21        @table_owner varchar(32) = null,
22        @table_qualifier varchar(32) = null,
23        @column_name varchar(771) = null)
24    AS
25        declare @full_table_name varchar(1543)
26        declare @table_id int
27        declare @char_bin_types varchar(32)
28    
29        declare @o_uid int
30        declare @o_name varchar(255)
31        declare @d_data_type smallint
32        declare @d_aux int
33        declare @d_ss_dtype tinyint
34        declare @d_type_name varchar(255)
35        declare @d_data_precision int
36        declare @d_numeric_scale smallint
37        declare @d_numeric_radix smallint
38        declare @d_sql_data_type smallint
39        declare @c_name varchar(255)
40        declare @c_length int
41        declare @c_prec tinyint
42        declare @c_scale tinyint
43        declare @c_type tinyint
44        declare @c_colid smallint
45        declare @c_status tinyint
46        declare @c_cdefault int
47        declare @xtname varchar(255)
48        declare @column_default varchar(1024)
49        declare @ident bit
50        declare @max_cdefault_len int
51        declare @cdefault_len int
52    
53        declare @startedInTransaction bit
54        if (@@trancount > 0)
55            select @startedInTransaction = 1
56        else
57            select @startedInTransaction = 0
58    
59    
60    
61        set transaction isolation level 1
62    
63        if (@startedInTransaction = 1)
64            save transaction odbc_keep_temptable_tx
65    
66        /* character and binary datatypes */
67        select @char_bin_types =
68            char(47) + char(39) + char(45) + char(37) + char(35) + char(34)
69    
70        if @column_name is null /*	If column name not supplied, match all */
71            select @column_name = '%'
72    
73        /* Check if the current database is the same as the one provided */
74        if @table_qualifier is not null
75        begin
76            if db_name() != @table_qualifier
77            begin /* 
78                ** If qualifier doesn't match current database 
79                ** 18039, Table qualifier must be name of current database
80                */
81                raiserror 18039
82                return (1)
83            end
84        end
85    
86        if @table_name is null
87        begin /*	If table name not supplied, match all */
88            select @table_name = '%'
89        end
90    
91        if @table_owner is null
92        begin /* If unqualified table name */
93            SELECT @full_table_name = @table_name
94        end
95        else
96        begin /* Qualified table name */
97            SELECT @full_table_name = @table_owner + '.' + @table_name
98        end
99    
100       /* Get Object ID */
101       SELECT @table_id = object_id(@full_table_name)
102   
103   
104       /* create the temp table to hold column results */
105       create table #odbc_columns(
106           TABLE_CAT varchar(32) null,
107           TABLE_SCHEM varchar(32) null,
108           TABLE_NAME varchar(255) null,
109           COLUMN_NAME varchar(255) null,
110           DATA_TYPE smallint null,
111           TYPE_NAME varchar(255) null,
112           COLUMN_SIZE int null,
113           BUFFER_LENGTH int null,
114           DECIMAL_DIGITS smallint null,
115           NUM_PREC_RADIX smallint null,
116           NULLABLE smallint null,
117           REMARKS varchar(255) null,
118           COLUMN_DEF varchar(512) null,
119           SQL_DATA_TYPE smallint null,
120           SQL_DATETIME_SUB smallint null,
121           CHAR_OCTET_LENGTH int null,
122           ORDINAL_POSITION int null,
123           IS_NULLABLE varchar(10) null)
124   
125       select @max_cdefault_len = 512
126   
127       /* If the table name parameter is valid, get the information */
128       if ((charindex('%', @full_table_name) = 0) and
129               (charindex('_', @full_table_name) = 0) and
130               @table_id != 0)
131       begin
132           declare odbc_columns_cursor1 cursor for
133           SELECT
134               c.cdefault,
135               c.colid,
136               c.length,
137               c.name,
138               c.prec,
139               c.scale,
140               c.status,
141               c.type,
142               d.aux,
143               d.data_precision,
144               d.data_type,
145               d.numeric_radix,
146               d.numeric_scale,
147               d.sql_data_type,
148               d.ss_dtype,
149               case
150                   when c.usertype = 80 then t.name
151                   when c.usertype = 24 then t.name
152                   when c.usertype = 25 then t.name
153                   else
154                       d.type_name
155               end,
156   
157               o.name,
158               o.uid,
159               xtname,
160               convert(bit, (c.status & 0x80))
161           FROM
162               syscolumns c,
163               sysobjects o,
164               sybsystemprocs.dbo.spt_datatype_info d,
165               sysxtypes x,
166               systypes t
167           WHERE
168               o.id = @table_id
169               AND c.id = o.id
170               /*
171               ** We use syscolumn.usertype instead of syscolumn.type
172               ** to do join with systypes.usertype. This is because
173               ** for a column which allows null, type stores its
174               ** Server internal datatype whereas usertype still
175               ** stores its user defintion datatype.  For an example,
176               ** a column of type 'decimal NULL', its usertype = 26,
177               ** representing decimal whereas its type = 106 
178               ** representing decimaln. nullable in the select list
179               ** already tells user whether the column allows null.
180               ** In the case of user defining datatype, this makes
181               ** more sense for the user.
182               */
183               AND c.usertype = t.usertype
184               AND t.type = d.ss_dtype
185               AND c.xtype *= x.xtid
186               AND o.type != 'P'
187               AND c.name like @column_name
188               --	AND d.ss_dtype IN (111, 109, 38, 110)	/* Just *N types */
189               AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */
190               AND c.usertype < 100 /* No user defined types */
191   
192           open odbc_columns_cursor1
193   
194           fetch odbc_columns_cursor1 into
195               @c_cdefault,
196               @c_colid,
197               @c_length,
198               @c_name,
199               @c_prec,
200               @c_scale,
201               @c_status,
202               @c_type,
203               @d_aux,
204               @d_data_precision,
205               @d_data_type,
206               @d_numeric_radix,
207               @d_numeric_scale,
208               @d_sql_data_type,
209               @d_ss_dtype,
210               @d_type_name,
211               @o_name,
212               @o_uid,
213               @xtname,
214               @ident
215   
216           while (@@sqlstatus = 0)
217           begin
218               if (@c_cdefault is NOT NULL)
219               begin
220                   exec sp_drv_column_default @c_cdefault, @column_default out
221                   select @cdefault_len = datalength(@column_default)
222   
223                   if (@cdefault_len > @max_cdefault_len)
224                       select @column_default = "TRUNCATED"
225   
226                   /* INTn, FLOATn, DATETIMEn and MONEYn types */
227                   INSERT INTO #odbc_columns values (
228                       DB_NAME(),
229                       USER_NAME(@o_uid),
230                       @o_name,
231                       @c_name,
232                       @d_data_type + convert(smallint,
233                       isnull(@d_aux,
234                           ascii(substring("666AAA@@@CB??GG",
235                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
236                           - 60)),
237                       /* TYPE_NAME */
238                       case
239                           when @ident = 1 then
240                           rtrim(substring(@d_type_name,
241                                   1 + isnull(@d_aux,
242                                       ascii(substring("III<<<MMMI<<A<A",
243                                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
244                                               1)) - 60), 18)) + ' identity'
245                           else
246                               rtrim(substring(@d_type_name,
247                                       1 + isnull(@d_aux,
248                                           ascii(substring("III<<<MMMI<<A<A",
249                                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
250                                                   1)) - 60), 18))
251                       end,
252                       isnull(convert(int, @c_prec),
253                           isnull(convert(int, @d_data_precision),
254                               convert(int, @c_length)))
255                       + isnull(@d_aux, convert(int,
256                           ascii(substring("???AAAFFFCKFOLS",
257                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
258                       isnull(convert(int, @c_length),
259                           convert(int, @c_length)) +
260                       convert(int, isnull(@d_aux,
261                           ascii(substring("AAA<BB<DDDHJSPP",
262                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
263                                   1)) - 64)),
264                       isnull(convert(smallint, @c_scale),
265                           convert(smallint, @d_numeric_scale))
266                       + convert(smallint,
267                       isnull(@d_aux,
268                           ascii(substring("<<<<<<<<<<<<<<?",
269                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
270                                   1)) - 60)),
271                       @d_numeric_radix,
272                       /* set nullability from status flag */
273                       convert(smallint, convert(bit, @c_status & 8)),
274                       convert(varchar(254), null), /* Remarks are NULL */
275                       @column_default,
276                       isnull(@d_sql_data_type,
277                           @d_data_type + convert(smallint,
278                           isnull(@d_aux,
279                               ascii(substring("666AAA@@@CB??GG",
280                                       2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
281                               - 60))),
282                       convert(smallint, NULL),
283                       /*
284                       ** if the datatype is of type CHAR or BINARY
285                       ** then set char_octet_length to the same value
286                       ** assigned in the "prec" column.
287                       **
288                       ** The first part of the logic is:
289                       **
290                       **   if(@c_type is in (47, 39, 45, 37, 35, 34))
291                       **       set char_octet_length = prec;
292                       **   else
293                       **       set char_octet_length = 0;
294                       */
295                       /*
296                       ** check if in the list
297                       ** if so, return a 1 and multiply it by the precision 
298                       ** if not, return a 0 and multiply it by the precision
299                       */
300                       convert(smallint,
301                       substring('0111111',
302                           charindex(char(@c_type),
303                               @char_bin_types) + 1, 1)) *
304                       /* calculate the precision */
305                       isnull(convert(int, @c_prec),
306                           isnull(convert(int, @d_data_precision),
307                               convert(int, @c_length)))
308                       + isnull(@d_aux, convert(int,
309                           ascii(substring('???AAAFFFCKFOLS',
310                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
311                       convert(int, @c_colid),
312                       rtrim(substring('NO YES',
313                               (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3)))
314   
315                   fetch odbc_columns_cursor1 into
316                       @c_cdefault,
317                       @c_colid,
318                       @c_length,
319                       @c_name,
320                       @c_prec,
321                       @c_scale,
322                       @c_status,
323                       @c_type,
324                       @d_aux,
325                       @d_data_precision,
326                       @d_data_type,
327                       @d_numeric_radix,
328                       @d_numeric_scale,
329                       @d_sql_data_type,
330                       @d_ss_dtype,
331                       @d_type_name,
332                       @o_name,
333                       @o_uid,
334                       @xtname,
335                       @ident
336   
337               end /* end of if	*/
338           end /* end of while */
339   
340           deallocate cursor odbc_columns_cursor1
341           declare odbc_columns_cursor2 cursor for
342           SELECT
343               c.cdefault,
344               c.colid,
345               c.length,
346               c.name,
347               c.prec,
348               c.scale,
349               c.status,
350               c.type,
351               d.aux,
352               d.data_precision,
353               d.data_type,
354               d.numeric_radix,
355               d.numeric_scale,
356               d.sql_data_type,
357               d.ss_dtype,
358               case
359                   when c.usertype = 80 then t.name
360                   when c.usertype = 24 then t.name
361                   when c.usertype = 25 then t.name
362                   else
363                       d.type_name
364               end,
365               o.name,
366               o.uid,
367               xtname,
368               convert(bit, (c.status & 0x80))
369           FROM
370               syscolumns c,
371               sysobjects o,
372               sybsystemprocs.dbo.spt_datatype_info d,
373               sysxtypes x,
374               systypes t
375           WHERE
376               o.id = @table_id
377               AND c.id = o.id
378               /*
379               ** We use syscolumn.usertype instead of syscolumn.type
380               ** to do join with systypes.usertype. This is because
381               ** for a column which allows null, type stores its
382               ** Server internal datatype whereas usertype still
383               ** stores its user defintion datatype.  For an example,
384               ** a column of type 'decimal NULL', its usertype = 26,
385               ** representing decimal whereas its type = 106 
386               ** representing decimaln. nullable in the select list
387               ** already tells user whether the column allows null.
388               ** In the case of user defining datatype, this makes
389               ** more sense for the user.
390               */
391               AND c.usertype = t.usertype
392               /*
393               ** We need a equality join with 
394               ** sybsystemprocs.dbo.spt_datatype_info here so that
395               ** there is only one qualified row returned from 
396               ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding
397               ** duplicates.
398               */
399               AND t.type = d.ss_dtype
400               AND c.xtype *= x.xtid
401               AND o.type != 'P'
402               AND c.name like @column_name
403               --	AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */
404               AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */
405                   OR c.usertype >= 100) /* User defined types */
406   
407           open odbc_columns_cursor2
408           fetch odbc_columns_cursor2 into
409               @c_cdefault,
410               @c_colid,
411               @c_length,
412               @c_name,
413               @c_prec,
414               @c_scale,
415               @c_status,
416               @c_type,
417               @d_aux,
418               @d_data_precision,
419               @d_data_type,
420               @d_numeric_radix,
421               @d_numeric_scale,
422               @d_sql_data_type,
423               @d_ss_dtype,
424               @d_type_name,
425               @o_name,
426               @o_uid,
427               @xtname,
428               @ident
429   
430           while (@@sqlstatus = 0)
431           begin
432               if (@c_cdefault is NOT NULL)
433               begin
434                   exec sp_drv_column_default @c_cdefault, @column_default out
435                   select @cdefault_len = datalength(@column_default)
436   
437                   if (@cdefault_len > @max_cdefault_len)
438                       select @column_default = "TRUNCATED"
439   
440                   /* All other types including user data types */
441                   INSERT INTO #odbc_columns values (
442                       DB_NAME(),
443                       USER_NAME(@o_uid),
444                       @o_name,
445                       @c_name,
446                       @d_data_type + convert(smallint,
447                       isnull(@d_aux,
448                           ascii(substring("666AAA@@@CB??GG",
449                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
450                           - 60)),
451                       /* TYPE_NAME */
452                       case
453                           when @ident = 1 then
454                           rtrim(substring(@d_type_name,
455                                   1 + isnull(@d_aux,
456                                       ascii(substring("III<<<MMMI<<A<A",
457                                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
458                                               1)) - 60), 18)) + ' identity'
459                           else
460                               rtrim(substring(@d_type_name,
461                                       1 + isnull(@d_aux,
462                                           ascii(substring("III<<<MMMI<<A<A",
463                                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
464                                                   1)) - 60), 18))
465                       end,
466                       isnull(convert(int, @c_prec),
467                           isnull(convert(int, @d_data_precision),
468                               convert(int, @c_length)))
469                       + isnull(@d_aux, convert(int,
470                           ascii(substring("???AAAFFFCKFOLS",
471                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
472                       isnull(convert(int, @c_length),
473                           convert(int, @c_length)) +
474                       convert(int, isnull(@d_aux,
475                           ascii(substring("AAA<BB<DDDHJSPP",
476                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
477                                   1)) - 64)),
478                       isnull(convert(smallint, @c_scale),
479                           convert(smallint, @d_numeric_scale)) +
480                       convert(smallint, isnull(@d_aux,
481                           ascii(substring("<<<<<<<<<<<<<<?",
482                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
483                                   1)) - 60)),
484                       @d_numeric_radix,
485                       /* set nullability from status flag */
486                       convert(smallint, convert(bit, @c_status & 8)),
487                       convert(varchar(254), null), /* Remarks are NULL */
488                       @column_default,
489                       isnull(@d_sql_data_type,
490                           @d_data_type + convert(smallint,
491                           isnull(@d_aux,
492                               ascii(substring("666AAA@@@CB??GG",
493                                       2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
494                               - 60))),
495                       convert(smallint, NULL),
496                       /*
497                       ** if the datatype is of type CHAR or BINARY
498                       ** then set char_octet_length to the same value
499                       ** assigned in the "prec" column.
500                       **
501                       ** The first part of the logic is:
502                       **
503                       **   if(@c_type is in (47, 39, 45, 37, 35, 34))
504                       **       set char_octet_length = prec;
505                       **   else
506                       **       set char_octet_length = 0;
507                       */
508                       /*
509                       ** check if in the list
510                       ** if so, return a 1 and multiply it by the precision 
511                       ** if not, return a 0 and multiply it by the precision
512                       */
513                       convert(smallint,
514                       substring('0111111',
515                           charindex(char(@c_type),
516                               @char_bin_types) + 1, 1)) *
517                       /* calculate the precision */
518                       isnull(convert(int, @c_prec),
519                           isnull(convert(int, @d_data_precision),
520                               convert(int, @c_length)))
521                       + isnull(@d_aux, convert(int,
522                           ascii(substring('???AAAFFFCKFOLS',
523                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
524                       convert(int, @c_colid),
525                       rtrim(substring('NO YES',
526                               (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3)))
527   
528                   fetch odbc_columns_cursor2 into
529                       @c_cdefault,
530                       @c_colid,
531                       @c_length,
532                       @c_name,
533                       @c_prec,
534                       @c_scale,
535                       @c_status,
536                       @c_type,
537                       @d_aux,
538                       @d_data_precision,
539                       @d_data_type,
540                       @d_numeric_radix,
541                       @d_numeric_scale,
542                       @d_sql_data_type,
543                       @d_ss_dtype,
544                       @d_type_name,
545                       @o_name,
546                       @o_uid,
547                       @xtname,
548                       @ident
549   
550               end /* end of if */
551   
552           end /* while loop */
553           deallocate cursor odbc_columns_cursor2
554       end
555       else
556       begin
557           /* 
558           ** This block is for the case where there IS pattern
559           ** matching done on the table name. 
560           */
561           if @table_owner is null /* If owner not supplied, match all */
562               select @table_owner = '%'
563   
564           declare odbc_columns_cursor3 cursor for
565           select
566               c.cdefault,
567               c.colid,
568               c.length,
569               c.name,
570               c.prec,
571               c.scale,
572               c.status,
573               c.type,
574               d.aux,
575               d.data_precision,
576               d.data_type,
577               d.numeric_radix,
578               d.numeric_scale,
579               d.sql_data_type,
580               d.ss_dtype,
581               case
582                   when c.usertype = 80 then t.name
583                   when c.usertype = 24 then t.name
584                   when c.usertype = 25 then t.name
585                   else
586                       d.type_name
587               end,
588               o.name,
589               o.uid,
590               xtname,
591               convert(bit, (c.status & 0x80))
592           FROM
593               syscolumns c,
594               sysobjects o,
595               sybsystemprocs.dbo.spt_datatype_info d,
596               sysxtypes x,
597               systypes t
598           WHERE
599               o.name like @table_name
600               AND user_name(o.uid) like @table_owner
601               AND o.id = c.id
602               /*
603               ** We use syscolumn.usertype instead of syscolumn.type
604               ** to do join with systypes.usertype. This is because
605               ** for a column which allows null, type stores its
606               ** Server internal datatype whereas usertype still
607               ** stores its user defintion datatype.  For an example,
608               ** a column of type 'decimal NULL', its usertype = 26,
609               ** representing decimal whereas its type = 106 
610               ** representing decimaln. nullable in the select list
611               ** already tells user whether the column allows null.
612               ** In the case of user defining datatype, this makes
613               ** more sense for the user.
614               */
615               AND c.usertype = t.usertype
616               AND t.type = d.ss_dtype
617               AND o.type != 'P'
618               AND c.xtype *= x.xtid
619               AND c.name like @column_name
620               --	AND d.ss_dtype IN (111, 109, 38, 110)	/* Just *N types */
621               AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */
622               AND c.usertype < 100
623   
624           open odbc_columns_cursor3
625   
626           fetch odbc_columns_cursor3 into
627               @c_cdefault,
628               @c_colid,
629               @c_length,
630               @c_name,
631               @c_prec,
632               @c_scale,
633               @c_status,
634               @c_type,
635               @d_aux,
636               @d_data_precision,
637               @d_data_type,
638               @d_numeric_radix,
639               @d_numeric_scale,
640               @d_sql_data_type,
641               @d_ss_dtype,
642               @d_type_name,
643               @o_name,
644               @o_uid,
645               @xtname,
646               @ident
647   
648   
649           /* INTn, FLOATn, DATETIMEn and MONEYn types */
650   
651           while (@@sqlstatus = 0)
652           begin
653               if (@c_cdefault is NOT NULL)
654               begin
655                   exec sp_drv_column_default @c_cdefault, @column_default out
656                   select @cdefault_len = datalength(@column_default)
657   
658                   if (@cdefault_len > @max_cdefault_len)
659                       select @column_default = "TRUNCATED"
660   
661                   /* INTn, FLOATn, DATETIMEn and MONEYn types */
662                   INSERT INTO #odbc_columns values (
663                       DB_NAME(),
664                       USER_NAME(@o_uid),
665                       @o_name,
666                       @c_name,
667                       @d_data_type + convert(smallint,
668                       isnull(@d_aux,
669                           ascii(substring("666AAA@@@CB??GG",
670                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
671                           - 60)),
672                       /* TYPE_NAME */
673                       case
674                           when @ident = 1 then
675                           rtrim(substring(@d_type_name,
676                                   1 + isnull(@d_aux,
677                                       ascii(substring("III<<<MMMI<<A<A",
678                                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
679                                               1)) - 60), 18)) + ' identity'
680                           else
681                               rtrim(substring(@d_type_name,
682                                       1 + isnull(@d_aux,
683                                           ascii(substring("III<<<MMMI<<A<A",
684                                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
685                                                   1)) - 60), 18))
686                       end,
687                       isnull(convert(int, @c_prec),
688                           isnull(convert(int, @d_data_precision),
689                               convert(int, @c_length)))
690                       + isnull(@d_aux, convert(int,
691                           ascii(substring("???AAAFFFCKFOLS",
692                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
693                       isnull(convert(int, @c_length),
694                           convert(int, @c_length)) +
695                       convert(int, isnull(@d_aux,
696                           ascii(substring("AAA<BB<DDDHJSPP",
697                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
698                                   1)) - 64)),
699                       isnull(convert(smallint, @c_scale),
700                           convert(smallint, @d_numeric_scale)) +
701                       convert(smallint, isnull(@d_aux,
702                           ascii(substring("<<<<<<<<<<<<<<?",
703                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
704                                   1)) - 60)),
705                       @d_numeric_radix,
706                       /* set nullability from status flag */
707                       convert(smallint, convert(bit, @c_status & 8)),
708                       convert(varchar(254), null), /* Remarks are NULL */
709                       @column_default,
710                       isnull(@d_sql_data_type,
711                           @d_data_type + convert(smallint,
712                           isnull(@d_aux,
713                               ascii(substring("666AAA@@@CB??GG",
714                                       2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
715                               - 60))),
716                       convert(smallint, NULL),
717                       /*
718                       ** if the datatype is of type CHAR or BINARY
719                       ** then set char_octet_length to the same value
720                       ** assigned in the "prec" column.
721                       **
722                       ** The first part of the logic is:
723                       **
724                       **   if(@c_type is in (47, 39, 45, 37, 35, 34))
725                       **       set char_octet_length = prec;
726                       **   else
727                       **       set char_octet_length = 0;
728                       */
729                       /*
730                       ** check if in the list
731                       ** if so, return a 1 and multiply it by the precision 
732                       ** if not, return a 0 and multiply it by the precision
733                       */
734                       convert(smallint,
735                       substring('0111111',
736                           charindex(char(@c_type),
737                               @char_bin_types) + 1, 1)) *
738                       /* calculate the precision */
739                       isnull(convert(int, @c_prec),
740                           isnull(convert(int, @d_data_precision),
741                               convert(int, @c_length)))
742                       + isnull(@d_aux, convert(int,
743                           ascii(substring('???AAAFFFCKFOLS',
744                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
745                       convert(int, @c_colid),
746                       rtrim(substring('NO YES',
747                               (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3)))
748                   fetch odbc_columns_cursor3 into
749                       @c_cdefault,
750                       @c_colid,
751                       @c_length,
752                       @c_name,
753                       @c_prec,
754                       @c_scale,
755                       @c_status,
756                       @c_type,
757                       @d_aux,
758                       @d_data_precision,
759                       @d_data_type,
760                       @d_numeric_radix,
761                       @d_numeric_scale,
762                       @d_sql_data_type,
763                       @d_ss_dtype,
764                       @d_type_name,
765                       @o_name,
766                       @o_uid,
767                       @xtname,
768                       @ident
769               end /* end of if	*/
770   
771           end /* while loop */
772   
773           deallocate cursor odbc_columns_cursor3
774           declare odbc_columns_cursor4 cursor for
775           SELECT
776               c.cdefault,
777               c.colid,
778               c.length,
779               c.name,
780               c.prec,
781               c.scale,
782               c.status,
783               c.type,
784               d.aux,
785               d.data_precision,
786               d.data_type,
787               d.numeric_radix,
788               d.numeric_scale,
789               d.sql_data_type,
790               d.ss_dtype,
791               case
792                   when c.usertype = 80 then t.name
793                   when c.usertype = 24 then t.name
794                   when c.usertype = 25 then t.name
795                   else
796                       d.type_name
797               end,
798               o.name,
799               o.uid,
800               xtname,
801               convert(bit, (c.status & 0x80))
802           FROM
803               syscolumns c,
804               sysobjects o,
805               sybsystemprocs.dbo.spt_datatype_info d,
806               sysxtypes x,
807               systypes t
808           WHERE
809               o.name like @table_name
810               AND user_name(o.uid) like @table_owner
811               AND o.id = c.id
812               /*
813               ** We use syscolumn.usertype instead of syscolumn.type
814               ** to do join with systypes.usertype. This is because
815               ** for a column which allows null, type stores its
816               ** Server internal datatype whereas usertype still
817               ** stores its user defintion datatype.  For an example,
818               ** a column of type 'decimal NULL', its usertype = 26,
819               ** representing decimal whereas its type = 106 
820               ** representing decimaln. nullable in the select list
821               ** already tells user whether the column allows null.
822               ** In the case of user defining datatype, this makes
823               ** more sense for the user.
824               */
825               AND c.usertype = t.usertype
826               /*
827               ** We need a equality join with 
828               ** sybsystemprocs.dbo.spt_datatype_info here so that
829               ** there is only one qualified row returned from 
830               ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding
831               ** duplicates.
832               */
833               AND t.type = d.ss_dtype
834               AND c.name like @column_name
835               AND o.type != 'P'
836               AND c.xtype *= x.xtid
837               AND c.name like @column_name
838               --	AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */
839               AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */
840                   OR c.usertype >= 100) /* User defined types */
841   
842           open odbc_columns_cursor4
843   
844           fetch odbc_columns_cursor4 into
845               @c_cdefault,
846               @c_colid,
847               @c_length,
848               @c_name,
849               @c_prec,
850               @c_scale,
851               @c_status,
852               @c_type,
853               @d_aux,
854               @d_data_precision,
855               @d_data_type,
856               @d_numeric_radix,
857               @d_numeric_scale,
858               @d_sql_data_type,
859               @d_ss_dtype,
860               @d_type_name,
861               @o_name,
862               @o_uid,
863               @xtname,
864               @ident
865   
866   
867           while (@@sqlstatus = 0)
868           begin
869               if (@c_cdefault is NOT NULL)
870               begin
871                   exec sp_drv_column_default @c_cdefault, @column_default out
872                   select @cdefault_len = datalength(@column_default)
873   
874                   if (@cdefault_len > @max_cdefault_len)
875                       select @column_default = "TRUNCATED"
876   
877                   /* All other types including user data types */
878                   INSERT INTO #odbc_columns values (
879                       /* TABLE_CAT */
880                       DB_NAME(),
881                       /* TABLE_SCHEM */
882                       USER_NAME(@o_uid),
883                       /* TABLE_NAME */
884                       @o_name,
885                       /*COLUMN_NAME*/
886                       @c_name,
887                       /* DATA_TYPE */
888                       @d_data_type + convert(smallint,
889                       isnull(@d_aux,
890                           ascii(substring("666AAA@@@CB??GG",
891                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
892                           - 60)),
893                       /* TYPE_NAME */
894                       case
895                           when @ident = 1 then
896                           rtrim(substring(@d_type_name,
897                                   1 + isnull(@d_aux,
898                                       ascii(substring("III<<<MMMI<<A<A",
899                                               2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
900                                               1)) - 60), 18)) + ' identity'
901                           else
902                               rtrim(substring(@d_type_name,
903                                       1 + isnull(@d_aux,
904                                           ascii(substring("III<<<MMMI<<A<A",
905                                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
906                                                   1)) - 60), 18))
907                       end,
908                       /* COLUMN_SIZE */
909                       isnull(convert(int, @c_prec),
910                           isnull(convert(int, @d_data_precision),
911                               convert(int, @c_length)))
912                       + isnull(@d_aux, convert(int,
913                           ascii(substring("???AAAFFFCKFOLS",
914                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
915                       /* BUFFER_LENGTH */
916                       isnull(convert(int, @c_length),
917                           convert(int, @c_length)) +
918                       convert(int, isnull(@d_aux,
919                           ascii(substring("AAA<BB<DDDHJSPP",
920                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
921                                   1)) - 64)),
922                       /* DECIMAL_DIGITS */
923                       isnull(convert(smallint, @c_scale),
924                           convert(smallint, @d_numeric_scale)) +
925                       convert(smallint, isnull(@d_aux,
926                           ascii(substring("<<<<<<<<<<<<<<?",
927                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length,
928                                   1)) - 60)),
929                       /* NUM_PREC_RADIX */
930                       @d_numeric_radix,
931                       /* NULLABLE */
932                       /* set nullability from status flag */
933                       convert(smallint, convert(bit, @c_status & 8)),
934                       /* REMARKS */
935                       convert(varchar(254), null),
936                       /* COLUMN_DEF */
937                       @column_default,
938                       /* SQL_DATA_TYPE */
939                       isnull(@d_sql_data_type,
940                           @d_data_type + convert(smallint,
941                           isnull(@d_aux,
942                               ascii(substring("666AAA@@@CB??GG",
943                                       2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1))
944                               - 60))),
945                       /* SQL_DATETIME_SUB */
946                       convert(smallint, NULL),
947                       /*
948                       ** if the datatype is of type CHAR or BINARY
949                       ** then set char_octet_length to the same value
950                       ** assigned in the "prec" column.
951                       **
952                       ** The first part of the logic is:
953                       **
954                       **   if(@c_type is in (47, 39, 45, 37, 35, 34))
955                       **       set char_octet_length = prec;
956                       **   else
957                       **       set char_octet_length = 0;
958                       */
959                       /*
960                       ** check if in the list
961                       ** if so, return a 1 and multiply it by the precision 
962                       ** if not, return a 0 and multiply it by the precision
963                       */
964                       /* CHAR_OCTET_LENGTH */
965                       convert(smallint,
966                       substring('0111111',
967                           charindex(char(@c_type),
968                               @char_bin_types) + 1, 1)) *
969                       /* calculate the precision */
970                       isnull(convert(int, @c_prec),
971                           isnull(convert(int, @d_data_precision),
972                               convert(int, @c_length)))
973                       + isnull(@d_aux, convert(int,
974                           ascii(substring('???AAAFFFCKFOLS',
975                                   2 * (@d_ss_dtype % 35 + 1) + 2 - 8 / @c_length, 1)) - 60)),
976                       /* ORDINAL_POSITION */
977                       convert(int, @c_colid),
978                       /* IS_NULLABLE */
979                       rtrim(substring('NO YES',
980                               (convert(smallint, convert(bit, @c_status & 8)) * 3) + 1, 3)))
981                   fetch odbc_columns_cursor4 into
982                       @c_cdefault,
983                       @c_colid,
984                       @c_length,
985                       @c_name,
986                       @c_prec,
987                       @c_scale,
988                       @c_status,
989                       @c_type,
990                       @d_aux,
991                       @d_data_precision,
992                       @d_data_type,
993                       @d_numeric_radix,
994                       @d_numeric_scale,
995                       @d_sql_data_type,
996                       @d_ss_dtype,
997                       @d_type_name,
998                       @o_name,
999                       @o_uid,
1000                      @xtname,
1001                      @ident
1002              end /* end of if 	*/
1003  
1004          end /* while loop */
1005  
1006          deallocate cursor odbc_columns_cursor4
1007      end /* Adaptive Server has expanded all '*' elements in the following statement */
1008      SELECT #odbc_columns.TABLE_CAT, #odbc_columns.TABLE_SCHEM, #odbc_columns.TABLE_NAME, #odbc_columns.COLUMN_NAME, #odbc_columns.DATA_TYPE, #odbc_columns.TYPE_NAME, #odbc_columns.COLUMN_SIZE, #odbc_columns.BUFFER_LENGTH, #odbc_columns.DECIMAL_DIGITS, #odbc_columns.NUM_PREC_RADIX, #odbc_columns.NULLABLE, #odbc_columns.REMARKS, #odbc_columns.COLUMN_DEF, #odbc_columns.SQL_DATA_TYPE, #odbc_columns.SQL_DATETIME_SUB, #odbc_columns.CHAR_OCTET_LENGTH, #odbc_columns.ORDINAL_POSITION, #odbc_columns.IS_NULLABLE FROM #odbc_columns ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION
1009      drop table #odbc_columns
1010      if (@startedInTransaction = 1)
1011          rollback transaction odbc_keep_temptable_tx
1012  
1013      return (0)
1014  


exec sp_procxmode 'sp_odbc_columns', 'AnyMode'
go

Grant Execute on sp_odbc_columns to public
go
RESULT SETS
sp_odbc_columns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 184
 QJWI 5 Join or Sarg Without Index 185
 QJWI 5 Join or Sarg Without Index 399
 QJWI 5 Join or Sarg Without Index 400
 QJWI 5 Join or Sarg Without Index 616
 QJWI 5 Join or Sarg Without Index 618
 QJWI 5 Join or Sarg Without Index 833
 QJWI 5 Join or Sarg Without Index 836
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysxtypes sybsystemprocs..sysxtypes
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 275
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 488
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 709
 MTYP 4 Assignment type mismatch COLUMN_DEF: varchar(512) = varchar(1024) 937
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 150
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 151
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 152
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 190
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 359
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 360
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 361
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 405
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 582
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 583
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 584
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 622
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 792
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 793
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 794
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 840
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause odbc_columns_cursor1 133
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause odbc_columns_cursor2 342
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause odbc_columns_cursor3 565
 CUNU 3 Cursor not updated: cursor should contain 'for read only' clause odbc_columns_cursor4 775
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 160
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 368
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 591
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 801
 MGTP 3 Grant to public sybsystemprocs..sp_odbc_columns  
 MGTP 3 Grant to public sybsystemprocs..spt_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 220
 MNER 3 No Error Check should check @@error after insert 227
 MNER 3 No Error Check should check return value of exec 434
 MNER 3 No Error Check should check @@error after insert 441
 MNER 3 No Error Check should check return value of exec 655
 MNER 3 No Error Check should check @@error after insert 662
 MNER 3 No Error Check should check return value of exec 871
 MNER 3 No Error Check should check @@error after insert 878
 MUCO 3 Useless Code Useless Brackets in create proc 19
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 82
 MUCO 3 Useless Code Useless Brackets 128
 MUCO 3 Useless Code Useless Brackets 216
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 223
 MUCO 3 Useless Code Useless Brackets 313
 MUCO 3 Useless Code Useless Brackets 430
 MUCO 3 Useless Code Useless Brackets 432
 MUCO 3 Useless Code Useless Brackets 437
 MUCO 3 Useless Code Useless Brackets 526
 MUCO 3 Useless Code Useless Brackets 651
 MUCO 3 Useless Code Useless Brackets 653
 MUCO 3 Useless Code Useless Brackets 658
 MUCO 3 Useless Code Useless Brackets 747
 MUCO 3 Useless Code Useless Brackets 867
 MUCO 3 Useless Code Useless Brackets 869
 MUCO 3 Useless Code Useless Brackets 874
 MUCO 3 Useless Code Useless Brackets 980
 MUCO 3 Useless Code Useless Brackets 1010
 MUCO 3 Useless Code Useless Brackets 1013
 QISO 3 Set isolation level 61
 QNAO 3 Not using ANSI Outer Join 161
 QNAO 3 Not using ANSI Outer Join 369
 QNAO 3 Not using ANSI Outer Join 592
 QNAO 3 Not using ANSI Outer Join 802
 QNUA 3 Should use Alias: Column xtname should use alias x 159
 QNUA 3 Should use Alias: Column xtname should use alias x 367
 QNUA 3 Should use Alias: Column xtname should use alias x 590
 QNUA 3 Should use Alias: Column xtname should use alias x 800
 QRPR 3 Repeated predicate c.name like @column_name 837
 CUPD 2 Updatable Cursor Marker (updatable by default) 133
 CUPD 2 Updatable Cursor Marker (updatable by default) 342
 CUPD 2 Updatable Cursor Marker (updatable by default) 565
 CUPD 2 Updatable Cursor Marker (updatable by default) 775
 MRST 2 Result Set Marker 1008
 MTR1 2 Metrics: Comments Ratio Comments: 26% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 59 = 59dec - 2exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 210 19

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..spt_datatype_info  
calls proc sybsystemprocs..sp_drv_column_default  
   reads table sybsystemprocs..syscomments  
reads table sybsystemprocs..sysxtypes  
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#odbc_columns (1)