DatabaseProcApplicationCreatedLinks
sybsystemprocssp_sproc_columns  14 déc. 14Defects Propagation Dependencies

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     
4     /*
5     ** Messages for "sp_sproc_columns"
6     **
7     ** 18039, "Table qualifier must be name of current database"
8     */
9     
10    create or replace procedure sp_sproc_columns
11        @procedure_name varchar(261) = '%', /* name of stored procedure  */
12        @procedure_owner varchar(257) = null, /* owner of stored procedure */
13        @procedure_qualifier varchar(257) = null, /* name of current database  */
14        @column_name varchar(257) = null /* col name or param name    */
15    as
16    
17        declare @msg varchar(250)
18        declare @group_num int
19        declare @semi_position int
20        declare @full_procedure_name char(520)
21        declare @procedure_id int
22        declare @char_bin_types varchar(30)
23        declare @sptlang int
24        declare @type_length int
25    
26        if @@trancount = 0
27        begin
28            set chained off
29        end
30    
31        set transaction isolation level 1
32    
33        select @sptlang = @@langid
34    
35        if @@langid != 0
36        begin
37            if not exists (
38                    select * from master.dbo.sysmessages where error
39                        between 17100 and 17109
40                        and langid = @@langid)
41                select @sptlang = 0
42        end
43    
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       select @type_length = length from systypes where type = 56
118   
119       if ((charindex('%', @full_procedure_name) = 0) and
120               (charindex('_', @full_procedure_name) = 0) and
121               @procedure_id != 0)
122       begin
123           /*
124           ** this block is for the case where there is no pattern
125           ** matching required for the table name
126           */
127           select /* INTn, FLOATn, DATETIMEn and MONEYn types */
128               procedure_qualifier = db_name(),
129               procedure_owner = user_name(o.uid),
130               procedure_name = o.name + ';' + ltrim(str(c.number, 5)),
131               column_name =
132               case
133                   when c.name = 'Return Type' then 'RETURN_VALUE'
134                   else c.name
135               end,
136               column_type =
137               case
138                   when c.name = 'Return Type'
139                   then convert(smallint, 5)
140                   else convert(smallint, 0)
141               end,
142   
143               /*
144               ** With the current data in the spt_datatype_info table, the convert() below
145               ** is never being used.
146               ** These conversions were ported from the original Microsoft INSTCAT.SQL
147               ** file which contained catalog stored procedures for 4.9 and earlier SQL
148               ** Servers.
149               */
150               data_type = d.data_type
151               + convert(smallint,
152               isnull(d.aux,
153                   ascii(substring("666AAA@@@CB??GG",
154                           2 * (d.ss_dtype % 35 + 1)
155                           + 2 - 8 / c.length,
156                           1)) - 60)),
157               type_name = rtrim(substring(d.type_name,
158                       1 + isnull(d.aux,
159                           ascii(substring("III<<<MMMI<<A<A",
160                                   2 * (d.ss_dtype % 35 + 1)
161                                   + 2 - 8 / c.length,
162                                   1)) - 60),
163                       13)),
164               "precision" = isnull(convert(int, c.prec),
165                   isnull(d.data_precision, convert(int, c.length)))
166               + isnull(d.aux, convert(int,
167                   ascii(substring("???AAAFFFCKFOLS",
168                           2 * (d.ss_dtype % 35 + 1)
169                           + 2 - 8 / c.length, 1))
170                   - 60)),
171               length = isnull(d.length, convert(int, c.length))
172               + convert(int, isnull(d.aux,
173                   ascii(substring("AAA<BB<DDDHJSPP",
174                           2 * (d.ss_dtype % 35
175                           + 1) + 2 - 8 / c.length,
176                           1)) - 64)),
177               scale = isnull(convert(smallint, c.scale),
178                   convert(smallint, d.numeric_scale)) +
179               convert(smallint,
180               isnull(d.aux, ascii(substring("<<<<<<<<<<<<<<?",
181                           2 * (d.ss_dtype % 35 + 1)
182                           + 2 - 8 / c.length,
183                           1)) - 60)),
184               radix = d.numeric_radix,
185               nullable = /* set nullability from status flag */
186               convert(smallint, convert(bit, c.status & 8)),
187               remarks = null, /* Remarks are NULL */
188               ss_data_type = c.type,
189               colid = c.colid,
190               column_def = NULL,
191               sql_data_type = isnull(d.sql_data_type,
192                   d.data_type + convert(smallint,
193                   isnull(d.aux,
194                       ascii(substring("666AAA@@@CB??GG",
195                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
196                       - 60))),
197               sql_datetime_sub = NULL,
198               char_octet_length =
199               /*
200               ** check if in the list
201               ** if so, return a 1 and multiply it by the precision
202               ** if not, return a 0 and multiply it by the precision
203               */
204               convert(smallint,
205               substring('0111111',
206                   charindex(char(c.type),
207                       @char_bin_types) + 1, 1)) *
208               /* calculate the precision */
209               isnull(convert(int, c.prec),
210                   isnull(convert(int, d.data_precision),
211                       convert(int, c.length)))
212               + isnull(d.aux, convert(int,
213                   ascii(substring('???AAAFFFCKFOLS',
214                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
215               ordinal_position = c.colid,
216               is_nullable = rtrim(substring('NO YES',
217                       (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)),
218               mode = case c.status2
219                   when NULL then "unknown"
220                   else (select convert(nvarchar(20), mi.description)
221                           from master.dbo.spt_values vi
222                           , master.dbo.sysmessages mi
223                           where vi.number = c.status2
224                               and vi.msgnum = mi.error
225                               and isnull(mi.langid, 0) = @sptlang
226                               and mi.error between 17583 and 17586)
227               end
228           from
229               syscolumns c,
230               sysobjects o,
231               sybsystemprocs.dbo.spt_datatype_info d
232           where
233               o.id = @procedure_id
234               and c.id = o.id
235               and c.type = d.ss_dtype
236               and c.name like @column_name
237               and d.ss_dtype in (111, 109, 38, 110, 43) /* Just *N types */
238               and c.number = @group_num
239           union
240           select
241               procedure_qualifier = db_name(),
242               procedure_owner = user_name(o.uid),
243               procedure_name = o.name + ';' + ltrim(str(@group_num, 5)),
244               column_name = 'RETURN_VALUE',
245               column_type = convert(smallint, 5), /* return parameter */
246               data_type = d.data_type + convert(smallint,
247               isnull(d.aux,
248                   ascii(substring("666AAA@@@CB??GG",
249                           2 * (d.ss_dtype % 35 + 1)
250                           + 2 - 8 / d.length, 1))
251                   - 60)),
252               type_name = d.type_name,
253               "precision" = isnull(d.data_precision, convert(int, d.length))
254               + isnull(d.aux, convert(int,
255                   ascii(substring("???AAAFFFCKFOLS",
256                           2 * (d.ss_dtype % 35 + 1)
257                           + 2 - 8 / d.length, 1))
258                   - 60)),
259               length = isnull(d.length, convert(int, @type_length))
260               + convert(int, isnull(d.aux,
261                   ascii(substring("AAA<BB<DDDHJSPP",
262                           2 * (d.ss_dtype % 35
263                           + 1) + 2 - 8 / @type_length,
264                           1)) - 64)),
265               scale = d.numeric_scale + convert(smallint,
266               isnull(d.aux,
267                   ascii(substring("<<<<<<<<<<<<<<?",
268                           2 * (d.ss_dtype % 35 + 1)
269                           + 2 - 8 / d.length,
270                           1)) - 60)),
271               radix = d.numeric_radix,
272               nullable = convert(smallint, 0),
273               remarks = null, /* Remarks are NULL */
274               ss_data_type = d.ss_dtype,
275               colid = convert(smallint, 0), /* first row returned */
276               column_def = NULL,
277               sql_data_type = isnull(d.sql_data_type,
278                   d.data_type + convert(smallint,
279                   isnull(d.aux,
280                       ascii(substring("666AAA@@@CB??GG",
281                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / d.length, 1))
282                       - 60))),
283               sql_datetime_sub = NULL,
284               char_octet_length = NULL,
285               ordinal_position = convert(tinyint, 0),
286               is_nullable = "NO",
287               mode = (select convert(nvarchar(20), mi.description)
288                   from master.dbo.sysmessages mi
289                   where isnull(mi.langid, 0) = @sptlang
290                       and mi.error = 17586)
291   
292           from
293               sysobjects o,
294               sybsystemprocs.dbo.spt_datatype_info d
295           where
296               o.id = @procedure_id
297               and d.ss_dtype = 56 /* int for return code */
298               and o.type = 'P'
299               and (@column_name = '%' or @column_name = 'RETURN_VALUE')
300           union
301           select /* All other types including user data types */
302               procedure_qualifier = db_name(),
303               procedure_owner = user_name(o.uid),
304               procedure_name = o.name + ';' + ltrim(str(c.number, 5)),
305               column_name =
306               case
307                   when c.name = 'Return Type' then 'RETURN_VALUE'
308                   else c.name
309               end,
310               column_type =
311               case
312                   when c.name = 'Return Type'
313                   then convert(smallint, 5)
314                   else convert(smallint, 0)
315               end,
316   
317               /*   Map systypes.type to ODBC type	       		*/
318               /*   SS-Type "				 1	      "	*/
319               /*	     "33 3 3 4 44 5 5 2 5 55666"	        */
320               /*	     "45 7 9	5 78 0 2 2 6 89012"             */
321               data_type = d.data_type + convert(smallint,
322               isnull(d.aux, ascii(substring("666AAA@@@CB??GG",
323                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
324               type_name =
325               case
326                   when t.name = 'extended type'
327                   then isnull(get_xtypename(c.xtype, c.xdbid),
328                       t.name)
329                   when t.usertype in (44, 45, 46)
330                   then "unsigned " + substring(t.name,
331                       charindex("u", t.name) + 1,
332                       charindex("t", t.name))
333                   else
334                       t.name
335               end,
336               "precision" = 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               length = isnull(d.length, convert(int, c.length))
344               + convert(int, isnull(d.aux,
345                   ascii(substring("AAA<BB<DDDHJSPP",
346                           2 * (d.ss_dtype % 35
347                           + 1) + 2 - 8 / c.length,
348                           1)) - 64)),
349               scale = isnull(convert(smallint, c.scale),
350                   convert(smallint, d.numeric_scale))
351               + convert(smallint,
352               isnull(d.aux,
353                   ascii(substring("<<<<<<<<<<<<<<?",
354                           2 * (d.ss_dtype % 35 + 1)
355                           + 2 - 8 / c.length,
356                           1)) - 60)),
357               radix = d.numeric_radix,
358   
359               /* set nullability from status flag */
360               nullable = convert(smallint, convert(bit, c.status & 8)),
361               remarks = null, /* Remarks are NULL */
362               ss_data_type = c.type,
363               colid = c.colid,
364               column_def = NULL,
365               sql_data_type = isnull(d.sql_data_type,
366                   d.data_type + convert(smallint,
367                   isnull(d.aux,
368                       ascii(substring("666AAA@@@CB??GG",
369                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
370                       - 60))),
371               sql_datetime_sub = NULL,
372               char_octet_length =
373               /*
374               ** check if in the list
375               ** if so, return a 1 and multiply it by the precision
376               ** if not, return a 0 and multiply it by the precision
377               */
378               convert(smallint,
379               substring('0111111',
380                   charindex(char(c.type),
381                       @char_bin_types) + 1, 1)) *
382               /* calculate the precision */
383               isnull(convert(int, c.prec),
384                   isnull(convert(int, d.data_precision),
385                       convert(int, c.length)))
386               + isnull(d.aux, convert(int,
387                   ascii(substring('???AAAFFFCKFOLS',
388                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
389               ordinal_position = c.colid,
390               is_nullable = rtrim(substring('NO YES',
391                       (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)),
392               mode = case c.status2
393                   when NULL then "unknown"
394                   else (select convert(nvarchar(20), mi.description)
395                           from master.dbo.spt_values vi
396                           , master.dbo.sysmessages mi
397                           where vi.number = c.status2
398                               and vi.msgnum = mi.error
399                               and isnull(mi.langid, 0) = @sptlang
400                               and mi.error between 17583 and 17586)
401               end
402           from
403               syscolumns c,
404               sysobjects o,
405               sybsystemprocs.dbo.spt_datatype_info d,
406               systypes t
407           where
408               o.id = @procedure_id
409               and c.id = o.id
410               and c.type *= d.ss_dtype
411               and c.usertype *= t.usertype
412               and c.name like @column_name
413               and c.number = @group_num
414               and d.ss_dtype not in (111, 109, 38, 110, 43) /* No *N types */
415           order by colid
416       end
417       else
418       begin
419           /* 
420           ** this block is for the case where there IS pattern
421           ** matching done on the table name
422           */
423           if @procedure_owner is null
424               select @procedure_owner = '%'
425   
426           select /* INTn, FLOATn, DATETIMEn and MONEYn types */
427               procedure_qualifier = db_name(),
428               procedure_owner = user_name(o.uid),
429               procedure_name = o.name + ';' + ltrim(str(c.number, 5)),
430               column_name =
431               case
432                   when c.name = 'Return Type' then 'RETURN_VALUE'
433                   else c.name
434               end,
435               column_type =
436               case
437                   when c.name = 'Return Type'
438                   then convert(smallint, 5)
439                   else convert(smallint, 0)
440               end,
441               data_type = d.data_type + convert(smallint,
442               isnull(d.aux,
443                   ascii(substring("666AAA@@@CB??GG",
444                           2 * (d.ss_dtype % 35 + 1)
445                           + 2 - 8 / c.length, 1))
446                   - 60)),
447               type_name = rtrim(substring(d.type_name,
448                       1 + isnull(d.aux,
449                           ascii(substring("III<<<MMMI<<A<A",
450                                   2 * (d.ss_dtype % 35 + 1)
451                                   + 2 - 8 / c.length,
452                                   1)) - 60), 13)),
453               "precision" = isnull(convert(int, c.prec),
454                   isnull(d.data_precision, convert(int, c.length)))
455               + isnull(d.aux, convert(int,
456                   ascii(substring("???AAAFFFCKFOLS",
457                           2 * (d.ss_dtype % 35 + 1)
458                           + 2 - 8 / c.length, 1))
459                   - 60)),
460               length = isnull(d.length, convert(int, c.length))
461               + convert(int, isnull(d.aux,
462                   ascii(substring("AAA<BB<DDDHJSPP",
463                           2 * (d.ss_dtype % 35 + 1)
464                           + 2 - 8 / c.length,
465                           1)) - 64)),
466               scale = isnull(convert(smallint, c.scale),
467                   convert(smallint, d.numeric_scale))
468               + convert(smallint,
469               isnull(d.aux,
470                   ascii(substring("<<<<<<<<<<<<<<?",
471                           2 * (d.ss_dtype % 35 + 1)
472                           + 2 - 8 / c.length,
473                           1)) - 60)),
474               radix = d.numeric_radix,
475               /* set nullability from status flag */
476               nullable = convert(smallint, convert(bit, c.status & 8)),
477               remarks = null, /* Remarks are NULL */
478               ss_data_type = c.type,
479               colid = c.colid,
480               column_def = NULL,
481               sql_data_type = isnull(d.sql_data_type,
482                   d.data_type + convert(smallint,
483                   isnull(d.aux,
484                       ascii(substring("666AAA@@@CB??GG",
485                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
486                       - 60))),
487               sql_datetime_sub = NULL,
488               char_octet_length =
489               /*
490               ** check if in the list
491               ** if so, return a 1 and multiply it by the precision
492               ** if not, return a 0 and multiply it by the precision
493               */
494               convert(smallint,
495               substring('0111111',
496                   charindex(char(c.type),
497                       @char_bin_types) + 1, 1)) *
498               /* calculate the precision */
499               isnull(convert(int, c.prec),
500                   isnull(convert(int, d.data_precision),
501                       convert(int, c.length)))
502               + isnull(d.aux, convert(int,
503                   ascii(substring('???AAAFFFCKFOLS',
504                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
505               ordinal_position = c.colid,
506               is_nullable = rtrim(substring('NO YES',
507                       (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)),
508               mode = case c.status2
509                   when NULL then "unknown"
510                   else (select convert(nvarchar(20), mi.description)
511                           from master.dbo.spt_values vi
512                           , master.dbo.sysmessages mi
513                           where vi.number = c.status2
514                               and vi.msgnum = mi.error
515                               and isnull(mi.langid, 0) = @sptlang
516                               and mi.error between 17583 and 17586)
517               end
518   
519           from
520               syscolumns c,
521               sysobjects o,
522               sybsystemprocs.dbo.spt_datatype_info d
523           where
524               o.name like @procedure_name
525               and user_name(o.uid) like @procedure_owner
526               and o.id = c.id
527               and c.type = d.ss_dtype
528               and c.name like @column_name
529   
530               /* Just procs & sqlj procs and funcs */
531               and o.type in ('P', 'F')
532               and d.ss_dtype in (111, 109, 38, 110, 43) /* Just *N types */
533           union
534           select distinct
535               procedure_qualifier = db_name(),
536               procedure_owner = user_name(o.uid),
537               procedure_name = (select object_name(p.id) + ';' +
538                   ltrim(str(p.number, 5)) from sysprocedures p
539                   where p.id = o.id and p.number = @group_num
540                   group by p.id, p.number),
541               column_name = 'RETURN_VALUE',
542               column_type = convert(smallint, 5), /* return parameter */
543               data_type = d.data_type + convert(smallint,
544               isnull(d.aux,
545                   ascii(substring("666AAA@@@CB??GG",
546                           2 * (d.ss_dtype % 35 + 1)
547                           + 2 - 8 / d.length, 1))
548                   - 60)),
549               type_name = d.type_name,
550               "precision" = isnull(d.data_precision, convert(int, d.length))
551               + isnull(d.aux, convert(int,
552                   ascii(substring("???AAAFFFCKFOLS",
553                           2 * (d.ss_dtype % 35 + 1)
554                           + 2 - 8 / d.length, 1))
555                   - 60)),
556               length = isnull(d.length, convert(int, @type_length))
557               + convert(int, isnull(d.aux,
558                   ascii(substring("AAA<BB<DDDHJSPP",
559                           2 * (d.ss_dtype % 35
560                           + 1) + 2 - 8 / @type_length,
561                           1)) - 64)),
562               scale = d.numeric_scale + convert(smallint,
563               isnull(d.aux,
564                   ascii(substring("<<<<<<<<<<<<<<?",
565                           2 * (d.ss_dtype % 35 + 1)
566                           + 2 - 8 / d.length,
567                           1)) - 60)),
568               radix = d.numeric_radix,
569               nullable = convert(smallint, 0),
570               remarks = null, /* Remarks are NULL */
571               ss_data_type = d.ss_dtype,
572               colid = convert(smallint, 0), /* first row returned */
573               column_def = NULL,
574               sql_data_type = isnull(d.sql_data_type,
575                   d.data_type + convert(smallint,
576                   isnull(d.aux,
577                       ascii(substring("666AAA@@@CB??GG",
578                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / d.length, 1))
579                       - 60))),
580               sql_datetime_sub = NULL,
581               char_octet_length = NULL,
582               ordinal_position = convert(tinyint, 0),
583               is_nullable = "NO",
584               mode = (select convert(nvarchar(20), mi.description)
585                   from master.dbo.sysmessages mi
586                   where isnull(mi.langid, 0) = @sptlang
587                       and mi.error = 17586)
588   
589           from
590               sysobjects o,
591               sybsystemprocs.dbo.spt_datatype_info d
592           where
593               o.name like @procedure_name
594               and user_name(o.uid) like @procedure_owner
595               and d.ss_dtype = 56 /* int for return code */
596               and o.type = 'P' /* Just Procedures */
597               and 'RETURN_VALUE' like @column_name
598           union
599           select /* All other types including user data types */
600               procedure_qualifier = db_name(),
601               procedure_owner = user_name(o.uid),
602               procedure_name = o.name + ';' + ltrim(str(c.number, 5)),
603               column_name =
604               case
605                   when c.name = 'Return Type' then 'RETURN_VALUE'
606                   else c.name
607               end,
608               column_type =
609               case
610                   when c.name = 'Return Type'
611                   then convert(smallint, 5)
612                   else convert(smallint, 0)
613               end,
614               /*   Map systypes.type to ODBC type    			*/
615               /*   SS-Type  "				 1	      " */
616               /*	      "33 3 3 4 44 5 5 2 5 55666"		*/
617               /*	      "45 7 9 5 78 0 2 2 6 89012"    		*/
618               data_type = d.data_type + convert(smallint,
619               isnull(d.aux, ascii(substring("666AAA@@@CB??GG",
620                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
621               type_name =
622               case
623                   when t.name = 'extended type'
624                   then isnull(get_xtypename(c.xtype, c.xdbid),
625                       t.name)
626                   when t.usertype in (44, 45, 46)
627                   then "unsigned " + substring(t.name,
628                       charindex("u", t.name) + 1,
629                       charindex("t", t.name))
630                   else
631                       t.name
632               end,
633   
634   
635               "precision" = isnull(convert(int, c.prec),
636                   isnull(d.data_precision, convert(int, c.length)))
637               + isnull(d.aux,
638                   convert(int,
639                   ascii(substring("???AAAFFFCKFOLS",
640                           2 * (d.ss_dtype % 35 + 1)
641                           + 2 - 8 / c.length, 1))
642                   - 60)),
643               length = isnull(d.length, convert(int, c.length))
644               + convert(int,
645               isnull(d.aux,
646                   ascii(substring("AAA<BB<DDDHJSPP",
647                           2 * (d.ss_dtype % 35 + 1)
648                           + 2 - 8 / c.length,
649                           1)) - 64)),
650               scale = isnull(convert(smallint, c.scale),
651                   convert(smallint, d.numeric_scale))
652               + convert(smallint,
653               isnull(d.aux,
654                   ascii(substring("<<<<<<<<<<<<<<?",
655                           2 * (d.ss_dtype % 35 + 1)
656                           + 2 - 8 / c.length,
657                           1)) - 60)),
658               radix = d.numeric_radix,
659               /* set nullability from status flag */
660               nullable = convert(smallint, convert(bit, c.status & 8)),
661               remarks = null, /* Remarks are NULL */
662               ss_data_type = c.type,
663               colid = c.colid,
664               column_def = NULL,
665               sql_data_type = isnull(d.sql_data_type,
666                   d.data_type + convert(smallint,
667                   isnull(d.aux,
668                       ascii(substring("666AAA@@@CB??GG",
669                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
670                       - 60))),
671               sql_datetime_sub = NULL,
672               char_octet_length =
673               /*
674               ** check if in the list
675               ** if so, return a 1 and multiply it by the precision
676               ** if not, return a 0 and multiply it by the precision
677               */
678               convert(smallint,
679               substring('0111111',
680                   charindex(char(c.type),
681                       @char_bin_types) + 1, 1)) *
682               /* calculate the precision */
683               isnull(convert(int, c.prec),
684                   isnull(convert(int, d.data_precision),
685                       convert(int, c.length)))
686               + isnull(d.aux, convert(int,
687                   ascii(substring('???AAAFFFCKFOLS',
688                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
689               ordinal_position = c.colid,
690               is_nullable = rtrim(substring('NO YES',
691                       (convert(smallint, convert(bit, c.status & 8)) * 3) + 1, 3)),
692               mode = case c.status2
693                   when NULL then "unknown"
694                   else (select convert(nvarchar(20), mi.description)
695                           from master.dbo.spt_values vi
696                           , master.dbo.sysmessages mi
697                           where vi.number = c.status2
698                               and vi.msgnum = mi.error
699                               and isnull(mi.langid, 0) = @sptlang
700                               and mi.error between 17583 and 17586)
701               end
702   
703           from
704               syscolumns c,
705               sysobjects o,
706               sybsystemprocs.dbo.spt_datatype_info d,
707               systypes t
708           where
709               o.name like @procedure_name
710               and user_name(o.uid) like @procedure_owner
711               and o.id = c.id
712               and c.type *= d.ss_dtype
713               and c.usertype *= t.usertype
714   
715               /* Just procs & sqlj procs and funcs */
716               and o.type in ('P', 'F')
717               and c.name like @column_name
718               and d.ss_dtype not in (111, 109, 38, 110, 43) /* No *N types */
719           order by procedure_owner, procedure_name, colid
720       end
721   
722   
723   


exec sp_procxmode 'sp_sproc_columns', 'AnyMode'
go

Grant Execute on sp_sproc_columns to public
go
RESULT SETS
sp_sproc_columns_rset_002
sp_sproc_columns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 235
 QJWI 5 Join or Sarg Without Index 410
 QJWI 5 Join or Sarg Without Index 411
 QJWI 5 Join or Sarg Without Index 527
 QJWI 5 Join or Sarg Without Index 712
 QJWI 5 Join or Sarg Without Index 713
 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
 MINU 4 Unique Index with nullable columns master..sysmessages master..sysmessages
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..spt_datatype_info d], 2 tables with... 240
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..sysobjects o and [sybsystemprocs..spt_datatype_info d], 3 tables with... 534
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
236
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {number}
412
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysprocedures.csysprocedures unique clustered
(id, number, type, sequence)
Intersection: {number}
539
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 40
 QTYP 4 Comparison type mismatch smallint = int 40
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 117
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 238
 QTYP 4 Comparison type mismatch smallint = int 238
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 297
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 413
 QTYP 4 Comparison type mismatch smallint = int 413
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 539
 QTYP 4 Comparison type mismatch smallint = int 539
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 595
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 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 233
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 234
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 234
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 296
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 408
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 409
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 409
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 526
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 526
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 537
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 539
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 539
 MAW1 3 Warning message on %name% sybsystemprocs..sysprocedures.id: Warning message on sysprocedures 540
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 711
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 711
 MGTP 3 Grant to public master..spt_values  
 MGTP 3 Grant to public master..sysmessages  
 MGTP 3 Grant to public sybsystemprocs..sp_sproc_columns  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MGTP 3 Grant to public sybsystemprocs..sysprocedures  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MLCH 3 Char type with length>30 char(520) 20
 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 119
 MUCO 3 Useless Code Useless Brackets 217
 MUCO 3 Useless Code Useless Brackets 391
 MUCO 3 Useless Code Useless Brackets 507
 MUCO 3 Useless Code Useless Brackets 691
 QAFM 3 Var Assignment from potentially many rows 117
 QCRS 3 Conditional Result Set 127
 QCRS 3 Conditional Result Set 426
 QDIS 3 Check correct use of 'select distinct' 534
 QGWO 3 Group by/Distinct/Union without order by 534
 QISO 3 Set isolation level 31
 QNAJ 3 Not using ANSI Inner Join 221
 QNAJ 3 Not using ANSI Inner Join 228
 QNAJ 3 Not using ANSI Inner Join 292
 QNAJ 3 Not using ANSI Inner Join 395
 QNAJ 3 Not using ANSI Inner Join 511
 QNAJ 3 Not using ANSI Inner Join 519
 QNAJ 3 Not using ANSI Inner Join 589
 QNAJ 3 Not using ANSI Inner Join 695
 QNAO 3 Not using ANSI Outer Join 402
 QNAO 3 Not using ANSI Outer Join 703
 QPNC 3 No column in condition 299
 QPNC 3 No column in condition 597
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
38
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {number}
Uncovered: [type]
223
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
224
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
289
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {number}
Uncovered: [type]
397
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
398
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {number}
Uncovered: [type]
513
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
514
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error, langid}
586
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {number}
Uncovered: [type]
697
 QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: sysmessages.ncsysmessages unique
(error, dlevel, langid)
Intersection: {error}
Uncovered: [dlevel]
698
 QUNI 3 Check Use of 'union' vs 'union all' 127
 QUNI 3 Check Use of 'union' vs 'union all' 426
 MRST 2 Result Set Marker 127
 MRST 2 Result Set Marker 426
 MSUB 2 Subquery Marker 37
 MSUB 2 Subquery Marker 108
 MSUB 2 Subquery Marker 287
 MSUB 2 Subquery Marker 584
 MSUC 2 Correlated Subquery Marker 220
 MSUC 2 Correlated Subquery Marker 394
 MSUC 2 Correlated Subquery Marker 510
 MSUC 2 Correlated Subquery Marker 537
 MSUC 2 Correlated Subquery Marker 694
 MTR1 2 Metrics: Comments Ratio Comments: 12% 10
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 19 = 19dec - 2exi + 2 10
 MTR3 2 Metrics: Query Complexity Complexity: 169 10
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0 127
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, m=master..sysmessages, sv=master..spt_values} 0 220
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, m=master..sysmessages, sv=master..spt_values} 0 394
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, o=sybsystemprocs..sysobjects, sdi=sybsystemprocs..spt_datatype_info} 0 426
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, m=master..sysmessages, sv=master..spt_values} 0 510
 PRED_QUERY_COLLECTION 2 {o=sybsystemprocs..sysobjects, p=sybsystemprocs..sysprocedures} 0 537
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, m=master..sysmessages, sv=master..spt_values} 0 694

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