DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_getprocedurecolumns  14 déc. 14Defects Propagation Dependencies

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


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..systypes t, sybsystemprocs..sysproc... 232
 QCAR 6 Cartesian product between tables sybsystemprocs..syscolumns c and [sybsystemprocs..systypes t] 560
 QJWI 5 Join or Sarg Without Index 241
 QJWI 5 Join or Sarg Without Index 447
 QJWI 5 Join or Sarg Without Index 448
 QJWI 5 Join or Sarg Without Index 569
 QJWI 5 Join or Sarg Without Index 787
 QJWI 5 Join or Sarg Without Index 788
 MEST 4 Empty String will be replaced by Single Space 54
 MEST 4 Empty String will be replaced by Single Space 60
 MEST 4 Empty String will be replaced by Single Space 61
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..spt_datatype_info d, sybsystemprocs... 246
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..systypes t, sybsystemprocs..spt_dat... 576
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
242
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
449
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 209
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 244
 QTYP 4 Comparison type mismatch smallint = int 244
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 254
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 303
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 304
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 352
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 422
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 450
 QTYP 4 Comparison type mismatch smallint = int 450
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 487
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 543
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 584
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 635
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 636
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 688
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 761
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 109
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 239
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 240
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 240
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 302
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 445
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 446
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 446
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 568
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 568
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 638
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 638
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 786
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 786
 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 68
 MUCO 3 Useless Code Useless Brackets 78
 MUCO 3 Useless Code Useless Brackets 118
 MUCO 3 Useless Code Useless Brackets 402
 MUCO 3 Useless Code Useless Brackets 406
 MUCO 3 Useless Code Useless Brackets 410
 MUCO 3 Useless Code Useless Brackets 741
 MUCO 3 Useless Code Useless Brackets 745
 MUCO 3 Useless Code Useless Brackets 749
 QCRS 3 Conditional Result Set 126
 QCRS 3 Conditional Result Set 464
 QDIS 3 Check correct use of 'select distinct' 576
 QGWO 3 Group by/Distinct/Union without order by 576
 QISO 3 Set isolation level 32
 QNAJ 3 Not using ANSI Inner Join 232
 QNAJ 3 Not using ANSI Inner Join 297
 QNAJ 3 Not using ANSI Inner Join 560
 QNAJ 3 Not using ANSI Inner Join 627
 QNAO 3 Not using ANSI Outer Join 439
 QNAO 3 Not using ANSI Outer Join 778
 QPNC 3 No column in condition 306
 QPNC 3 No column in condition 639
 QTJ1 3 Table only appears in inner join clause 631
 QUNI 3 Check Use of 'union' vs 'union all' 126
 QUNI 3 Check Use of 'union' vs 'union all' 464
 VUNU 3 Variable is not used @sptlang 25
 MRST 2 Result Set Marker 126
 MRST 2 Result Set Marker 464
 MSUB 2 Subquery Marker 108
 MTR1 2 Metrics: Comments Ratio Comments: 16% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 15 = 15dec - 2exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 168 12
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0 126
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0 464
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 576

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