DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_getprocedurecolumns  31 Aug 14Defects Dependencies

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


exec sp_procxmode 'sp_odbc_getprocedurecolumns', 'AnyMode'
go

Grant Execute on sp_odbc_getprocedurecolumns to public
go
RESULT SETS
sp_odbc_getprocedurecolumns_rset_002
sp_odbc_getprocedurecolumns_rset_001

DEFECTS
 QCAR 6 Cartesian product between tables sybsystemprocs..syscolumns c and [sybsystemprocs..sysprocedures p, sybsystemprocs..sy... 230
 QCAR 6 Cartesian product between tables sybsystemprocs..syscolumns c and [sybsystemprocs..systypes t] 558
 QJWI 5 Join or Sarg Without Index 239
 QJWI 5 Join or Sarg Without Index 445
 QJWI 5 Join or Sarg Without Index 446
 QJWI 5 Join or Sarg Without Index 567
 QJWI 5 Join or Sarg Without Index 785
 QJWI 5 Join or Sarg Without Index 786
 MEST 4 Empty String will be replaced by Single Space 52
 MEST 4 Empty String will be replaced by Single Space 58
 MEST 4 Empty String will be replaced by Single Space 59
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..spt_datatype_info d, sybsystemprocs... 244
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..spt_datatype_info d, sybsystemprocs... 574
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
240
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
447
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 207
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 242
 QTYP 4 Comparison type mismatch smallint = int 242
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 252
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 301
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 302
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 350
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 420
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 448
 QTYP 4 Comparison type mismatch smallint = int 448
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 485
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 541
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 582
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 633
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 634
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 686
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 759
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 MGTP 3 Grant to public sybsystemprocs..sp_odbc_getprocedurecolumns  
 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..sysprocedures  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MNER 3 No Error Check should check return value of exec 66
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 400
 MUCO 3 Useless Code Useless Brackets 404
 MUCO 3 Useless Code Useless Brackets 408
 MUCO 3 Useless Code Useless Brackets 739
 MUCO 3 Useless Code Useless Brackets 743
 MUCO 3 Useless Code Useless Brackets 747
 QCRS 3 Conditional Result Set 124
 QCRS 3 Conditional Result Set 462
 QDIS 3 Check correct use of 'select distinct' 574
 QGWO 3 Group by/Distinct/Union without order by 574
 QISO 3 Set isolation level 30
 QNAJ 3 Not using ANSI Inner Join 230
 QNAJ 3 Not using ANSI Inner Join 295
 QNAJ 3 Not using ANSI Inner Join 558
 QNAJ 3 Not using ANSI Inner Join 625
 QNAO 3 Not using ANSI Outer Join 437
 QNAO 3 Not using ANSI Outer Join 776
 QPNC 3 No column in condition 304
 QPNC 3 No column in condition 637
 QTJ1 3 Table only appears in inner join clause 629
 QUNI 3 Check Use of 'union' vs 'union all' 124
 QUNI 3 Check Use of 'union' vs 'union all' 462
 VUNU 3 Variable is not used @sptlang 23
 MRST 2 Result Set Marker 124
 MRST 2 Result Set Marker 462
 MSUB 2 Subquery Marker 106
 MTR1 2 Metrics: Comments Ratio Comments: 16% 10
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 14dec - 2exi + 2 10
 MTR3 2 Metrics: Query Complexity Complexity: 168 10
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0 124
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0 462
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 574

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..sysprocedures  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..spt_datatype_info  
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table master..syslanguages (1)  
reads table sybsystemprocs..systypes