Database	Proc	Application	Created	Links
sybsystemprocs	sp_oledb_getprocedurecolumns		31 Aug 14	Defects Dependencies

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



exec sp_procxmode 'sp_oledb_getprocedurecolumns', 'AnyMode'
go

Grant Execute on sp_oledb_getprocedurecolumns to public
go

RESULT SETS
sp_oledb_getprocedurecolumns_rset_002
sp_oledb_getprocedurecolumns_rset_001

DEFECTS
QCAR 6 Cartesian product between tables sybsystemprocs..syscolumns c and [sybsystemprocs..systypes t, sybsystemprocs..sysproc...	240
QCAR 6 Cartesian product between tables sybsystemprocs..syscolumns c and [sybsystemprocs..systypes t]	513
QJWI 5 Join or Sarg Without Index	250
QJWI 5 Join or Sarg Without Index	413
QJWI 5 Join or Sarg Without Index	414
QJWI 5 Join or Sarg Without Index	523
QJWI 5 Join or Sarg Without Index	693
QJWI 5 Join or Sarg Without Index	694
MEST 4 Empty String will be replaced by Single Space	85
MEST 4 Empty String will be replaced by Single Space	91
MEST 4 Empty String will be replaced by Single Space	92
MINU 4 Unique Index with nullable columns master..sysmessages	master..sysmessages
MTYP 4 Assignment type mismatch NUMERIC_PRECISION: smallint = int	212
MTYP 4 Assignment type mismatch NUMERIC_PRECISION: smallint = int	483
MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int	706
QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..systypes t, sybsystemprocs..spt_dat...	256
QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..systypes t, sybsystemprocs..spt_dat...	530
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {number}	251
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {number}	415
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	49
QTYP 4 Comparison type mismatch smallint = int	49
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	253
QTYP 4 Comparison type mismatch smallint = int	253
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	299
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	300
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	382
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	393
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	416
QTYP 4 Comparison type mismatch smallint = int	416
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	574
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	575
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	658
QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int	672
TNOI 4 Table with no index sybsystemprocs..spt_datatype_info	sybsystemprocs..spt_datatype_info
MGTP 3 Grant to public master..sysmessages
MGTP 3 Grant to public sybsystemprocs..sp_oledb_getprocedurecolumns
MGTP 3 Grant to public sybsystemprocs..spt_datatype_info
MGTP 3 Grant to public sybsystemprocs..spt_sybdrv
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	99
MNER 3 No Error Check should check @@error after insert	157
MNER 3 No Error Check should check @@error after insert	430
MNER 3 No Error Check should check @@error after update	706
MUCO 3 Useless Code Useless Brackets	27
MUCO 3 Useless Code Useless Brackets	39
MUCO 3 Useless Code Useless Brackets	109
MUCO 3 Useless Code Useless Brackets	149
MUCO 3 Useless Code Useless Brackets	709
MUCO 3 Useless Code Useless Brackets	713
MUCO 3 Useless Code Useless Brackets	737
QCRS 3 Conditional Result Set	711
QCRS 3 Conditional Result Set	715
QDIS 3 Check correct use of 'select distinct'	530
QGWO 3 Group by/Distinct/Union without order by	530
QISO 3 Set isolation level	37
QJWT 3 Join or Sarg Without Index on temp table	708
QNAJ 3 Not using ANSI Inner Join	240
QNAJ 3 Not using ANSI Inner Join	292
QNAJ 3 Not using ANSI Inner Join	513
QNAJ 3 Not using ANSI Inner Join	565
QNAJ 3 Not using ANSI Inner Join	706
QNAO 3 Not using ANSI Outer Join	404
QNAO 3 Not using ANSI Outer Join	683
QPNC 3 No column in condition	302
QPNC 3 No column in condition	578
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique (error, dlevel, langid) Intersection: {error, langid}	47
QTJ1 3 Table only appears in inner join clause	569
QUNI 3 Check Use of 'union' vs 'union all'	158
QUNI 3 Check Use of 'union' vs 'union all'	431
VNRD 3 Variable is not read @sptlang	50
MRST 2 Result Set Marker	711
MRST 2 Result Set Marker	715
MSUB 2 Subquery Marker	46
MSUB 2 Subquery Marker	139
MTR1 2 Metrics: Comments Ratio Comments: 13%	11
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 23 = 23dec - 2exi + 2	11
MTR3 2 Metrics: Query Complexity Complexity: 192	11
PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0	158
PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0	431
PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0	530