DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_getprocedurecolumns  14 déc. 14Defects Propagation 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 or replace 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                   when t.usertype > 100 and c.type = d.ss_dtype
389                   then d.type_name
390                   else
391                       t.name
392               end,
393               LOCAL_TYPE_NAME = case when t.name = 'extended type'
394                   then isnull(get_xtypename(c.xtype, c.xdbid), t.name)
395                   when t.type = 58 then "smalldatetime"
396                   when t.usertype in (44, 45, 46)
397                   then "unsigned " + substring(t.name,
398                       charindex("u", t.name) + 1,
399                       charindex("t", t.name))
400                   else t.name
401               end
402   
403   
404   
405   
406           from
407               syscolumns c,
408               sysobjects o,
409               sybsystemprocs.dbo.spt_datatype_info d,
410               systypes t
411   
412           where
413               o.id = @procedure_id
414               and c.id = o.id
415               and c.type *= d.ss_dtype
416               and c.usertype *= t.usertype
417               and c.name like @parameter_name
418               and c.number = @group_num
419               and d.ss_dtype not in (111, 109, 38, 110) /* No *N types */
420   
421   
422           order by convert(int, colid)
423       end
424       else
425       begin
426           /* 
427           ** this block is for the case where there IS pattern
428           ** matching done on the table name
429           */
430           if @procedure_schema is null
431               select @procedure_schema = '%'
432           insert #oledb_results_table
433           select /* INTn, FLOATn, DATETIMEn and MONEYn types */
434               PROCEDURE_CATALOG = db_name(),
435               PROCEDURE_SCHEMA = user_name(o.uid),
436               PROCEDURE_NAME = o.name,
437               PARAMETER_NAME =
438               case
439                   when c.name = 'Return Type' then 'RETURN_VALUE'
440                   else c.name
441               end,
442               ORDINAL_POSITION = convert(int, c.colid),
443               PARAMETER_TYPE =
444               case
445                   when c.name = 'Return Type'
446                   then convert(smallint, 4) /*DBPARAMTYPE_RETURNVALUE*/
447                   when c.status2 = 1
448                   then convert(smallint, 1) /*DBPARAMTYPE_INPUT*/
449                   when c.status2 = 2
450                   then convert(smallint, 3) /*DBPARAMTYPE_OUTPUT*/
451                   when c.status2 = 4
452                   then convert(smallint, 2) /*DBPARAMTYPE_INPUTOUTPUT*/
453                   else null
454               end,
455               PARAMETER_HASDEFAULT = convert(bit, 0),
456               PARAMETER_DEFAULT = convert(varchar(255), null),
457               IS_NULLABLE = convert(smallint, 1),
458               DATA_TYPE = 0,
459               CHARACTER_MAXIMUM_LENGTH = isnull(convert(int, c.prec),
460                   isnull(d.data_precision, convert(int, c.length)))
461               + isnull(d.aux, convert(int,
462                   ascii(substring("???AAAFFFCKFOLS",
463                           2 * (d.ss_dtype % 35 + 1)
464                           + 2 - 8 / c.length, 1))
465                   - 60)),
466               CHARACTER_OCTET_LENGTH =
467               /*
468               ** check if in the list
469               ** if so, return a 1 and multiply it by the precision
470               ** if not, return a 0 and multiply it by the precision
471               */
472               convert(smallint,
473               substring('0111111',
474                   charindex(char(c.type),
475                       @char_bin_types) + 1, 1)) *
476               /* calculate the precision */
477               isnull(convert(int, c.prec),
478                   isnull(convert(int, d.data_precision),
479                       convert(int, c.length)))
480               + isnull(d.aux, convert(int,
481                   ascii(substring('???AAAFFFCKFOLS',
482                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
483   
484   
485               NUMERIC_PRECISION = d.data_precision,
486               NUMERIC_PRECISION_RADIX = d.numeric_radix,
487               NUMERIC_SCALE = isnull(convert(smallint, c.scale),
488                   convert(smallint, d.numeric_scale))
489               + convert(smallint,
490               isnull(d.aux,
491                   ascii(substring("<<<<<<<<<<<<<<?",
492                           2 * (d.ss_dtype % 35 + 1)
493                           + 2 - 8 / c.length,
494                           1)) - 60)),
495   
496               /* set nullability from status flag */
497   
498               DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */
499               TYPE_NAME = rtrim(substring(d.type_name,
500                       1 + isnull(d.aux,
501                           ascii(substring("III<<<MMMI<<A<A",
502                                   2 * (d.ss_dtype % 35 + 1)
503                                   + 2 - 8 / c.length,
504                                   1)) - 60), 18)),
505               LOCAL_TYPE_NAME = rtrim(substring(d.type_name,
506                       1 + isnull(d.aux,
507                           ascii(substring("III<<<MMMI<<A<A",
508                                   2 * (d.ss_dtype % 35 + 1)
509                                   + 2 - 8 / c.length,
510                                   1)) - 60), 18))
511   
512   
513   
514   
515           from
516               syscolumns c,
517               sysobjects o,
518               sybsystemprocs.dbo.spt_datatype_info d,
519               systypes t
520   
521           where
522               o.name like @procedure_name
523               and user_name(o.uid) like @procedure_schema
524               and o.id = c.id
525               and c.type = d.ss_dtype
526               and c.name like @parameter_name
527   
528               /* Just procs & sqlj procs and funcs */
529               and o.type in ('P', 'F')
530               and d.ss_dtype in (111, 109, 38, 110) /* Just *N types */
531           union
532           select distinct
533               PROCEDURE_CATALOG = db_name(),
534               PROCEDURE_SCHEMA = user_name(o.uid),
535               PROCEDURE_NAME = o.name,
536               PARAMETER_NAME = 'RETURN_VALUE',
537               ORDINAL_POSITION = convert(tinyint, 0),
538               PARAMETER_TYPE = convert(smallint, 4), /* return parameter */
539               PARAMETER_HASDEFAULT = convert(bit, 0),
540               PARAMETER_DEFAULT = convert(varchar(255), null),
541               IS_NULLABLE = convert(smallint, 1),
542               DATA_TYPE = 0,
543               CHARACTER_MAXIMUM_LENGTH = isnull(d.data_precision, convert(int, d.length))
544               + isnull(d.aux, convert(int,
545                   ascii(substring("???AAAFFFCKFOLS",
546                           2 * (d.ss_dtype % 35 + 1)
547                           + 2 - 8 / d.length, 1))
548                   - 60)),
549               CHARACTER_OCTET_LENGTH = NULL,
550   
551               NUMERIC_PRECISION = d.data_precision,
552               NUMERIC_PRECISION_RADIX = d.numeric_radix,
553               NUMERIC_SCALE = d.numeric_scale + convert(smallint,
554               isnull(d.aux,
555                   ascii(substring("<<<<<<<<<<<<<<?",
556                           2 * (d.ss_dtype % 35 + 1)
557                           + 2 - 8 / d.length,
558                           1)) - 60)),
559   
560   
561               DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */
562               TYPE_NAME = d.type_name,
563               LOCAL_TYPE_NAME = d.type_name
564   
565   
566   
567           from
568               sysobjects o,
569               sybsystemprocs.dbo.spt_datatype_info d,
570               systypes t,
571               sysprocedures p
572   
573           where
574               o.name like @procedure_name
575               and user_name(o.uid) like @procedure_schema
576               and d.ss_dtype = 56 /* int for return code */
577               and t.type = 56
578               and o.type = 'P' /* Just Procedures */
579               and p.id = o.id
580               and 'RETURN_VALUE' like @parameter_name
581           union
582           select /* All other types including user data types */
583               PROCEDURE_CATALOG = db_name(),
584               PROCEDURE_SCHEMA = user_name(o.uid),
585               PROCEDURE_NAME = o.name,
586               PARAMETER_NAME =
587               case
588                   when c.name = 'Return Type' then 'RETURN_VALUE'
589                   else c.name
590               end,
591               ORDINAL_POSITION = convert(int, c.colid),
592               PARAMETER_TYPE =
593               case
594                   when c.name = 'Return Type'
595                   then convert(smallint, 4)
596                   when c.status2 = 1
597                   then convert(smallint, 1)
598                   when c.status2 = 2
599                   then convert(smallint, 3)
600                   when c.status2 = 4
601                   then convert(smallint, 2)
602                   else null
603               end,
604               PARAMETER_HASDEFAULT = convert(bit, 0),
605               PARAMETER_DEFAULT = convert(varchar(255), null),
606               IS_NULLABLE = convert(smallint, 1),
607               DATA_TYPE = 0,
608               CHARACTER_MAXIMUM_LENGTH =
609               case
610                   when d.data_precision = 0
611                   then convert(int, 0)
612                   else
613                       isnull(convert(int, c.prec),
614                           isnull(d.data_precision, convert(int, c.length)))
615                       + isnull(d.aux,
616                           convert(int,
617                           ascii(substring("???AAAFFFCKFOLS",
618                                   2 * (d.ss_dtype % 35 + 1)
619                                   + 2 - 8 / c.length, 1))
620                           - 60))
621               end,
622               CHARACTER_OCTET_LENGTH =
623               /*
624               ** check if in the list
625               ** if so, return a 1 and multiply it by the precision
626               ** if not, return a 0 and multiply it by the precision
627               */
628               convert(smallint,
629               substring('0111111',
630                   charindex(char(c.type),
631                       @char_bin_types) + 1, 1)) *
632               /* calculate the precision */
633               isnull(convert(int, c.prec),
634                   isnull(convert(int, d.data_precision),
635                       convert(int, c.length)))
636               + isnull(d.aux, convert(int,
637                   ascii(substring('???AAAFFFCKFOLS',
638                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
639   
640   
641               NUMERIC_PRECISION = d.data_precision,
642               NUMERIC_PRECISION_RADIX = d.numeric_radix,
643               NUMERIC_SCALE = isnull(convert(smallint, c.scale),
644                   convert(smallint, d.numeric_scale))
645               + convert(smallint,
646               isnull(d.aux,
647                   ascii(substring("<<<<<<<<<<<<<<?",
648                           2 * (d.ss_dtype % 35 + 1)
649                           + 2 - 8 / c.length,
650                           1)) - 60)),
651   
652               /* set nullability from status flag */
653   
654               DESCRIPTION = convert(varchar(254), null), /* Remarks are NULL */
655               TYPE_NAME =
656               case
657                   when t.name = 'extended type'
658                   then isnull(get_xtypename(c.xtype, c.xdbid),
659                       t.name)
660                   when t.type = 58
661                   then "smalldatetime"
662                   when t.usertype in (44, 45, 46)
663                   then "unsigned " + substring(t.name,
664                       charindex("u", t.name) + 1,
665                       charindex("t", t.name))
666                   when t.usertype > 100 and c.type = d.ss_dtype
667                   then d.type_name
668                   else
669                       t.name
670               end,
671               LOCAL_TYPE_NAME =
672               case
673                   when t.name = 'extended type'
674                   then isnull(get_xtypename(c.xtype, c.xdbid),
675                       t.name)
676                   when t.type = 58
677                   then "smalldatetime"
678                   when t.usertype in (44, 45, 46)
679                   then "unsigned " + substring(t.name,
680                       charindex("u", t.name) + 1,
681                       charindex("t", t.name))
682                   else
683                       t.name
684               end
685   
686   
687           from
688               syscolumns c,
689               sysobjects o,
690               sybsystemprocs.dbo.spt_datatype_info d,
691               systypes t
692   
693           where
694               o.name like @procedure_name
695               and user_name(o.uid) like @procedure_schema
696               and o.id = c.id
697               and c.type *= d.ss_dtype
698               and c.usertype *= t.usertype
699   
700               /* Just procs & sqlj procs and funcs */
701               and o.type in ('P', 'F')
702               and c.name like @parameter_name
703               and d.ss_dtype not in (111, 109, 38, 110) /* No *N types */
704   
705   
706           order by PROCEDURE_SCHEMA, PROCEDURE_NAME, convert(int, colid)
707       end
708   
709   
710       update #oledb_results_table set o.DATA_TYPE = m.data_type from
711           sybsystemprocs.dbo.spt_sybdrv m, #oledb_results_table o
712       where o.TYPE_NAME = m.type_name
713       if (@is_ado = 2)
714       begin
715           /* 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
716       end
717       else if (@is_ado = 1)
718       begin
719           select
720               PROCEDURE_CATALOG,
721               PROCEDURE_SCHEMA,
722               PROCEDURE_NAME,
723               PARAMETER_NAME,
724               ORDINAL_POSITION,
725               PARAMETER_TYPE,
726               PARAMETER_HASDEFAULT,
727               PARAMETER_DEFAULT,
728               IS_NULLABLE,
729               DATA_TYPE,
730               CHARACTER_MAXIMUM_LENGTH,
731               CHARACTER_OCTET_LENGTH,
732               NUMERIC_PRECISION,
733               NUMERIC_SCALE,
734               DESCRIPTION,
735               TYPE_NAME,
736               LOCAL_TYPE_NAME
737           from #oledb_results_table order by PROCEDURE_CATALOG, PROCEDURE_SCHEMA, PROCEDURE_NAME
738   
739       end
740   
741       if (@startedInTransaction = 1)
742           rollback transaction oledb_keep_temptable_tx
743   
744   


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..sysprocedures p, sybsystemprocs..sy... 240
 QCAR 6 Cartesian product between tables sybsystemprocs..syscolumns c and [sybsystemprocs..systypes t] 515
 QJWI 5 Join or Sarg Without Index 250
 QJWI 5 Join or Sarg Without Index 415
 QJWI 5 Join or Sarg Without Index 416
 QJWI 5 Join or Sarg Without Index 525
 QJWI 5 Join or Sarg Without Index 697
 QJWI 5 Join or Sarg Without Index 698
 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 485
 MTYP 4 Assignment type mismatch DATA_TYPE: smallint = int 710
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..spt_datatype_info d, sybsystemprocs... 256
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..systypes t, sybsystemprocs..spt_dat... 532
 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}
417
 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: smallint vs int 388
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 395
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 418
 QTYP 4 Comparison type mismatch smallint = int 418
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 576
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 577
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 660
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 666
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 676
 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 140
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 248
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 249
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 249
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 298
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 413
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 414
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 414
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 524
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 524
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 579
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 579
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 696
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 696
 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 432
 MNER 3 No Error Check should check @@error after update 710
 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 713
 MUCO 3 Useless Code Useless Brackets 717
 MUCO 3 Useless Code Useless Brackets 741
 QCRS 3 Conditional Result Set 715
 QCRS 3 Conditional Result Set 719
 QDIS 3 Check correct use of 'select distinct' 532
 QGWO 3 Group by/Distinct/Union without order by 532
 QISO 3 Set isolation level 37
 QNAJ 3 Not using ANSI Inner Join 240
 QNAJ 3 Not using ANSI Inner Join 292
 QNAJ 3 Not using ANSI Inner Join 515
 QNAJ 3 Not using ANSI Inner Join 567
 QNAJ 3 Not using ANSI Inner Join 710
 QNAO 3 Not using ANSI Outer Join 406
 QNAO 3 Not using ANSI Outer Join 687
 QPNC 3 No column in condition 302
 QPNC 3 No column in condition 580
 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 571
 QUNI 3 Check Use of 'union' vs 'union all' 158
 QUNI 3 Check Use of 'union' vs 'union all' 433
 VNRD 3 Variable is not read @sptlang 50
 MRST 2 Result Set Marker 715
 MRST 2 Result Set Marker 719
 MSUB 2 Subquery Marker 46
 MSUB 2 Subquery Marker 139
 MTR1 2 Metrics: Comments Ratio Comments: 12% 11
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 24 = 24dec - 2exi + 2 11
 MTR3 2 Metrics: Query Complexity Complexity: 196 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 433
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 532

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_oledb_getprocedurecolumns_rset_001 
reads table sybsystemprocs..spt_datatype_info  
reads table sybsystemprocs..sysobjects  
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..sysprocedures  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..sysmessages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
reads table master..sysmessages (1)  
writes table sybsystemprocs..sp_oledb_getprocedurecolumns_rset_002 
read_writes table tempdb..#oledb_results_table (1) 
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..spt_sybdrv