DatabaseProcApplicationCreatedLinks
sybsystemprocssp_oledb_columns  14 déc. 14Defects Propagation Dependencies

1     
2     
3     
4     /* --------------COLUMNS ------------------------------------- */
5     
6     
7     
8     /* Sccsid = "%Z% generic/sproc/%M% %I% %G% " */
9     /*      10.0        07/20/93        sproc/columns */
10    
11    
12    /* This is the version for servers which support UNION */
13    
14    /* This routine is intended for support of oledb connectivity.  Under no
15    ** circumstances should changes be made to this routine unless they are
16    ** to fix oledb related problems.  All other users are at there own risk!
17    **
18    ** Please be aware that any changes made to this file (or any other oledb
19    ** support routine) will require Sybase to recertify the SQL server as
20    ** oledb compliant.  This process is currently being managed internally
21    ** by the "Interoperability Engineering Technology Solutions Group" here
22    ** within Sybase.
23    */
24    
25    CREATE OR REPLACE PROCEDURE sp_oledb_columns(
26        @table_name varchar(771) = null,
27        @table_owner varchar(32) = null,
28        @table_qualifier varchar(32) = null,
29        @column_name varchar(771) = null,
30        @is_ado int = 1,
31        @stripblanks int = 1)
32    AS
33        declare @full_table_name varchar(1543)
34        declare @table_id int
35        declare @char_bin_types varchar(32)
36        declare @startedInTransaction bit
37    
38        if (@@trancount > 0)
39            select @startedInTransaction = 1
40        else
41            select @startedInTransaction = 0
42    
43        set transaction isolation level 1
44    
45        if (@startedInTransaction = 1)
46            save transaction oledb_keep_temptable_tx
47    
48        /* character and binary datatypes */
49        select @char_bin_types =
50            char(47) + char(39) + char(45) + char(37) + char(35) + char(34)
51        create table #results_table
52        (TABLE_CATALOG varchar(32) null,
53            TABLE_SCHEMA varchar(32) null,
54            TABLE_NAME varchar(255) null,
55            COLUMN_NAME varchar(255) null,
56            COLUMN_GUID varchar(36) null,
57            COLUMN_PROPID int null,
58            ORDINAL_POSITION int null,
59            COLUMN_HASDEFAULT bit default 1 not null,
60            COLUMN_DEFAULT varchar(255) null,
61            COLUMN_FLAGS int null,
62            usertype int null,
63            IS_NULLABLE bit default 1 not null,
64            DATA_TYPE smallint null,
65            TYPE_GUID varchar(36) null,
66            CHARACTER_MAXIMUM_LENGTH int null,
67            CHARACTER_OCTET_LENGTH int null,
68            NUMERIC_PRECISION smallint null,
69            NUMERIC_PRECISION_RADIX smallint null,
70            TYPE_NAME varchar(255) null,
71            NUMERIC_SCALE smallint null,
72            DATETIME_PRECISION int null,
73            CHARACTER_SET_CATALOG varchar(32) null,
74            CHARACTER_SET_SCHEMA varchar(32) null,
75            CHARACTER_SET_NAME varchar(255) null,
76            COLLATION_CATALOG varchar(32) null,
77            COLLATION_SCHEMA varchar(32) null,
78            COLLATION_NAME varchar(32) null,
79            DOMAIN_CATALOG varchar(32) null,
80            DOMAIN_SCHEMA varchar(32) null,
81            DOMAIN_NAME varchar(32) null,
82            DESCRIPTION varchar(32) null,
83            tds_type smallint null,
84            id int null,
85            col_len int null,
86            sys_type smallint null,
87            row_id numeric(10) identity)
88    
89        if @column_name is null /*	If column name not supplied, match all */
90            select @column_name = '%'
91    
92        /* Check if the current database is the same as the one provided */
93        if @table_qualifier is not null
94        begin
95    
96            if db_name() != @table_qualifier
97            begin /* 
98                ** If qualifier doesn't match current database 
99                ** force a no-row selection
100               */
101               goto SelectClause
102           end
103       end
104   
105       if @table_name is null
106       begin /*	If table name not supplied, match all */
107           select @table_name = '%'
108       end
109   
110       if @table_owner is null
111       begin /* If unqualified table name */
112           SELECT @full_table_name = @table_name
113       end
114       else
115       begin /* Qualified table name */
116           SELECT @full_table_name = @table_owner + '.' + @table_name
117       end
118   
119       /* Get Object ID */
120       SELECT @table_id = object_id(@full_table_name)
121   
122   
123       /* If the table name parameter is valid, get the information */
124       if ((charindex('%', @full_table_name) = 0) and
125               (charindex('_', @full_table_name) = 0) and
126               @table_id != 0)
127       begin
128   
129           insert into #results_table
130           SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */
131               TABLE_CATALOG = DB_NAME(),
132               TABLE_SCHEMA = USER_NAME(o.uid),
133               TABLE_NAME = o.name,
134               COLUMN_NAME = c.name,
135               COLUMN_GUID = convert(varchar(36), null),
136               COLUMN_PROPID = convert(int, null),
137               ORDINAL_POSITION = convert(int, c.colid),
138               COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1),
139               COLUMN_DEFAULT = convert(varchar(254), null),
140               /*CT: IsNullable 0x20 (c.status&8 * 4),
141               MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable
142               IsFixedLength 0x10 - yes
143               IsLong 0x80 - no
144               */
145               COLUMN_FLAGS = convert(int, c.status & 8) * 12 + 16,
146               usertype = t.usertype,
147               IS_NULLABLE = convert(bit, c.status & 8),
148               DATA_TYPE = convert(smallint, m.data_type),
149               TYPE_GUID = convert(varchar(36), null),
150               CHARACTER_MAXIMUM_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */
151               CHARACTER_OCTET_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */
152               /* CT: MONEYN, INTN, DATETIMN and FLOATN will all be included */
153               NUMERIC_PRECISION = isnull(convert(smallint, c.prec),
154                   isnull(convert(smallint, d.data_precision),
155                       convert(smallint, c.length)))
156               + isnull(d.aux, convert(smallint,
157                   ascii(substring("???AAAFFFCKFOLS",
158                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
159   
160               NUMERIC_PRECISION_RADIX = d.numeric_radix,
161               TYPE_NAME =
162               case
163                   when convert(bit, (c.status & 0x80)) = 1 then
164                   rtrim(substring(d.type_name,
165                           1 + isnull(d.aux,
166                               ascii(substring('III<<<MMMI<<A<A',
167                                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
168                                       1)) - 60), 18)) + ' identity'
169                   else
170                       rtrim(substring(d.type_name,
171                               1 + isnull(d.aux,
172                                   ascii(substring('III<<<MMMI<<A<A',
173                                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
174                                           1)) - 60), 18))
175               end,
176               NUMERIC_SCALE = convert(smallint, null), /*CT: only for decimal and numeric in oledb*/
177               /*CT: in ss_dtype 111, 109, 38, 110, only 111 has non null value for this item
178               it is the datetime type scale, UI4 type*/
179               DATETIME_PRECISION = case
180                   when d.ss_dtype = 111 /*CT:  also the type is changed to UI4*/
181                   then
182                   isnull(convert(int, c.scale),
183                       convert(int, d.numeric_scale))
184                   + convert(int,
185                   isnull(d.aux,
186                       ascii(substring("<<<<<<<<<<<<<<?",
187                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
188                               1)) - 60))
189                   else
190                       convert(int, null)
191               end,
192   
193               CHARACTER_SET_CATALOG = null,
194               CHARACTER_SET_SCHEMA = null,
195               CHARACTER_SET_NAME = null,
196               COLLATION_CATALOG = null,
197               COLLATION_SCHEMA = null,
198               COLLATION_NAME = null,
199               DOMAIN_CATALOG = null,
200               DOMAIN_SCHEMA = null,
201               DOMAIN_NAME = null,
202               DESCRIPTION = null, /* Description are NULL */
203               tds_type = c.type,
204               id = c.id,
205               col_len = c.length,
206               sys_type = t.type
207   
208           FROM
209               syscolumns c,
210               sysobjects o,
211               sybsystemprocs.dbo.spt_datatype_info d,
212               systypes t,
213               sybsystemprocs.dbo.spt_sybdrv m
214           WHERE
215               o.id = @table_id
216               AND c.id = o.id
217               /*
218               ** We use syscolumn.usertype instead of syscolumn.type
219               ** to do join with systypes.usertype. This is because
220               ** for a column which allows null, type stores its
221               ** Server internal datatype whereas usertype still
222               ** stores its user defintion datatype.  For an example,
223               ** a column of type 'decimal NULL', its usertype = 26,
224               ** representing decimal whereas its type = 106 
225               ** representing decimaln. nullable in the select list
226               ** already tells user whether the column allows null.
227               ** In the case of user defining datatype, this makes
228               ** more sense for the user.
229               */
230               AND c.usertype = t.usertype
231               AND t.type = d.ss_dtype
232               AND c.name like @column_name
233               --	AND d.ss_dtype IN (111, 109, 38, 110)	/* Just *N types */
234               AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */
235               AND c.usertype < 100 /* No user defined types */
236               AND m.type_name =
237               rtrim(substring(d.type_name,
238                       1 + isnull(d.aux,
239                           ascii(substring("III<<<MMMI<<A<A",
240                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
241                                   1)) - 60), 18))
242           UNION
243           SELECT /* All other types including user data types */
244               TABLE_CATALOG = DB_NAME(),
245               TABLE_SCHEMA = USER_NAME(o.uid),
246               TABLE_NAME = o.name,
247               COLUMN_NAME = c.name,
248               COLUMN_GUID = convert(varchar(36), null),
249               COLUMN_PROPID = convert(int, null),
250               ORDINAL_POSITION = convert(int, c.colid),
251               COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1),
252               COLUMN_DEFAULT = convert(varchar(254), null),
253               /*CT: IsNullable 0x20 (c.status&8 * 4),
254               MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable
255               IsFixedLength 0x10 - yes
256               IsLong 0x80 - no
257               */
258               COLUMN_FLAGS = convert(int, c.status & 8) * 12 +
259               case when c.type in (37, 39, 155, 34, 35, 174) then 0
260                   when c.type in (47) and (convert(int, c.status & 8) = 1) then 0
261                   else 16 end +
262               case when c.type in (34, 35, 174) then 128 /*text, image and unitext type*/
263                   else 0 end,
264               usertype = t.usertype,
265               IS_NULLABLE = convert(bit, c.status & 8),
266               DATA_TYPE = convert(smallint, m.data_type),
267               TYPE_GUID = convert(varchar(36), null),
268               /*CT: only for char, binary and bit in oledb, see spec*/
269               CHARACTER_MAXIMUM_LENGTH = case
270                   when c.type in (135, 155)
271                   then
272                   (isnull(convert(int, c.prec),
273                       isnull(convert(int, d.data_precision),
274                           convert(int, c.length)))
275                   + isnull(d.aux, convert(int,
276                       ascii(substring("???AAAFFFCKFOLS",
277                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))) / 2
278                   when c.type in (47, 39, 45, 37, 35, 34, 50)
279                   then
280                   isnull(convert(int, c.prec),
281                       isnull(convert(int, d.data_precision),
282                           convert(int, c.length)))
283                   + isnull(d.aux, convert(int,
284                       ascii(substring("???AAAFFFCKFOLS",
285                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
286                   else
287                       convert(int, null)
288               end,
289   
290               /*
291               ** if the datatype is of type CHAR or BINARY
292               ** then set char_octet_length to the same value
293               ** assigned in the "prec" column.
294               **
295               ** The first part of the logic is:
296               **
297               **   if(c.type is in (47, 39, 45, 37, 35, 34))
298               **       set char_octet_length = prec;
299               **   if (c.type is in (135, 155)
300               **       set char_octet_length = prec * 2;
301               **   else
302               **       set char_octet_length = null;
303               */
304               CHARACTER_OCTET_LENGTH = case
305                   when c.type in (47, 39, 45, 37, 35, 34, 50)
306                   then /*same size as the CHARACTER_MAXIMUM_LENGTH */
307                   isnull(convert(int, c.prec),
308                       isnull(convert(int, d.data_precision),
309                           convert(int, c.length)))
310                   + isnull(d.aux, convert(int,
311                       ascii(substring("???AAAFFFCKFOLS",
312                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
313                   when c.type in (135, 155)
314                   then /* CHARACTER_MAXIMUM_LENGTH * 2 for the unichar, univarchar*/
315                   isnull(convert(int, c.prec),
316                       isnull(convert(int, d.data_precision),
317                           convert(int, c.length)))
318                   + isnull(d.aux, convert(int,
319                       ascii(substring("???AAAFFFCKFOLS",
320                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
321                   else
322                       convert(int, null)
323               end,
324               NUMERIC_PRECISION = case
325                   when c.type in (47, 39, 45, 37, 35, 34, 135, 155, 50, 58, 61, 123, 147, 174)
326                   then convert(smallint, null)
327                   else
328                       isnull(convert(smallint, c.prec),
329                           isnull(convert(smallint, d.data_precision),
330                               convert(smallint, c.length)))
331                       + isnull(d.aux, convert(smallint,
332                           ascii(substring("???AAAFFFCKFOLS",
333                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
334               end,
335               NUMERIC_PRECISION_RADIX = d.numeric_radix,
336               TYPE_NAME =
337               case
338                   when convert(bit, (c.status & 0x80)) = 1 then
339                   rtrim(substring(d.type_name,
340                           1 + isnull(d.aux,
341                               ascii(substring('III<<<MMMI<<A<A',
342                                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
343                                       1)) - 60), 18)) + ' identity'
344                   else
345                       rtrim(substring(d.type_name,
346                               1 + isnull(d.aux,
347                                   ascii(substring('III<<<MMMI<<A<A',
348                                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
349                                           1)) - 60), 18))
350               end,
351   
352               NUMERIC_SCALE = case
353                   when c.type in (63, 108, 55, 106) then
354                   isnull(convert(smallint, c.scale),
355                       convert(smallint, d.numeric_scale)) +
356                   convert(smallint, isnull(d.aux,
357                       ascii(substring("<<<<<<<<<<<<<<?",
358                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
359                               1)) - 60))
360                   else
361                       convert(smallint, null)
362               end,
363               DATETIME_PRECISION = case
364                   when c.type in (58, 61, 123, 147, 111) then
365                   isnull(convert(int, c.scale),
366                       convert(int, d.numeric_scale)) +
367                   convert(int, isnull(d.aux,
368                       ascii(substring("<<<<<<<<<<<<<<?",
369                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
370                               1)) - 60))
371                   else
372                       convert(int, null)
373               end,
374               CHARACTER_SET_CATALOG = null,
375               CHARACTER_SET_SCHEMA = null,
376               CHARACTER_SET_NAME = null,
377               COLLATION_CATALOG = null,
378               COLLATION_SCHEMA = null,
379               COLLATION_NAME = null,
380               DOMAIN_CATALOG = null,
381               DOMAIN_SCHEMA = null,
382               DOMAIN_NAME = null,
383               DESCRIPTION = null, /* Description are NULL */
384               tds_type = c.type,
385               id = c.id,
386               col_len = c.length,
387               sys_type = t.type
388   
389   
390           FROM
391               syscolumns c,
392               sysobjects o,
393               sybsystemprocs.dbo.spt_datatype_info d,
394               systypes t,
395               sybsystemprocs.dbo.spt_sybdrv m
396           WHERE
397               o.id = @table_id
398               AND c.id = o.id
399               /*
400               ** We use syscolumn.usertype instead of syscolumn.type
401               ** to do join with systypes.usertype. This is because
402               ** for a column which allows null, type stores its
403               ** Server internal datatype whereas usertype still
404               ** stores its user defintion datatype.  For an example,
405               ** a column of type 'decimal NULL', its usertype = 26,
406               ** representing decimal whereas its type = 106 
407               ** representing decimaln. nullable in the select list
408               ** already tells user whether the column allows null.
409               ** In the case of user defining datatype, this makes
410               ** more sense for the user.
411               */
412               AND c.usertype = t.usertype
413               /*
414               ** We need a equality join with 
415               ** sybsystemprocs.dbo.spt_datatype_info here so that
416               ** there is only one qualified row returned from 
417               ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding
418               ** duplicates.
419               */
420               AND t.type = d.ss_dtype
421               AND c.name like @column_name
422               --	AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */
423               AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */
424   
425                   OR c.usertype >= 100) /* User defined types */
426               AND m.type_name =
427               rtrim(substring(d.type_name,
428                       1 + isnull(d.aux,
429                           ascii(substring("III<<<MMMI<<A<A",
430                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
431                                   1)) - 60), 18))
432   
433           ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
434       end
435       else
436       begin
437           /* 
438           ** This block is for the case where there IS pattern
439           ** matching done on the table name. 
440           */
441           if @table_owner is null /* If owner not supplied, match all */
442               select @table_owner = '%'
443   
444           insert into #results_table
445           SELECT /* INTn, FLOATn, DATETIMEn and MONEYn types */
446               TABLE_CATALOG = DB_NAME(),
447               TABLE_SCHEMA = USER_NAME(o.uid),
448               TABLE_NAME = o.name,
449               COLUMN_NAME = c.name,
450               COLUMN_GUID = convert(varchar(36), null),
451               COLUMN_PROPID = convert(int, null),
452               ORDINAL_POSITION = convert(int, c.colid),
453               COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1),
454               COLUMN_DEFAULT = convert(varchar(254), null),
455               /*CT: IsNullable 0x20 (c.status&8 * 4),
456               MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable
457               IsFixedLength 0x10 - yes
458               IsLong 0x80 - no
459               */
460               COLUMN_FLAGS = convert(int, c.status & 8) * 12 + 16,
461               usertype = t.usertype,
462               IS_NULLABLE = convert(bit, c.status & 8),
463               DATA_TYPE = convert(smallint, m.data_type),
464               TYPE_GUID = convert(varchar(36), null),
465               CHARACTER_MAXIMUM_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */
466               CHARACTER_OCTET_LENGTH = convert(int, null), /*CT: only for char, binary and bit in oledb */
467               /* CT: MONEYN, INTN, DATETIMN and FLOATN will all be included */
468               NUMERIC_PRECISION = isnull(convert(smallint, c.prec),
469                   isnull(convert(smallint, d.data_precision),
470                       convert(smallint, c.length)))
471               + isnull(d.aux, convert(smallint,
472                   ascii(substring("???AAAFFFCKFOLS",
473                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60)),
474               NUMERIC_PRECISION_RADIX = d.num_prec_radix,
475               TYPE_NAME =
476               case
477                   when convert(bit, (c.status & 0x80)) = 1 then
478                   rtrim(substring(d.type_name,
479                           1 + isnull(d.aux,
480                               ascii(substring('III<<<MMMI<<A<A',
481                                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
482                                       1)) - 60), 18)) + ' identity'
483                   else
484                       rtrim(substring(d.type_name,
485                               1 + isnull(d.aux,
486                                   ascii(substring('III<<<MMMI<<A<A',
487                                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
488                                           1)) - 60), 18))
489               end,
490               NUMERIC_SCALE = convert(smallint, null), /*CT: only for decimal and numeric in oledb*/
491               /*CT: in ss_dtype 111, 109, 38, 110, only 111 has non null value for this item
492               it is the datetime type scale, UI4 type*/
493               DATETIME_PRECISION = case
494                   when d.ss_dtype = 111 /*CT:  also the type is changed to UI4*/
495                   then
496                   isnull(convert(int, c.scale),
497                       convert(int, d.numeric_scale))
498                   + convert(int,
499                   isnull(d.aux,
500                       ascii(substring("<<<<<<<<<<<<<<?",
501                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
502                               1)) - 60))
503                   else
504                       convert(int, null)
505               end,
506               CHARACTER_SET_CATALOG = null,
507               CHARACTER_SET_SCHEMA = null,
508               CHARACTER_SET_NAME = null,
509               COLLATION_CATALOG = null,
510               COLLATION_SCHEMA = null,
511               COLLATION_NAME = null,
512               DOMAIN_CATALOG = null,
513               DOMAIN_SCHEMA = null,
514               DOMAIN_NAME = null,
515               DESCRIPTION = null, /* Description are NULL */
516               tds_type = c.type,
517               id = c.id,
518               col_len = c.length,
519               sys_type = t.type
520   
521           FROM
522               syscolumns c,
523               sysobjects o,
524               sybsystemprocs.dbo.spt_datatype_info d,
525               systypes t,
526               sybsystemprocs.dbo.spt_sybdrv m
527           WHERE
528               o.name like @table_name
529               AND user_name(o.uid) like @table_owner
530               AND o.id = c.id
531               /*
532               ** We use syscolumn.usertype instead of syscolumn.type
533               ** to do join with systypes.usertype. This is because
534               ** for a column which allows null, type stores its
535               ** Server internal datatype whereas usertype still
536               ** stores its user defintion datatype.  For an example,
537               ** a column of type 'decimal NULL', its usertype = 26,
538               ** representing decimal whereas its type = 106 
539               ** representing decimaln. nullable in the select list
540               ** already tells user whether the column allows null.
541               ** In the case of user defining datatype, this makes
542               ** more sense for the user.
543               */
544               AND c.usertype = t.usertype
545               AND t.type = d.ss_dtype
546               AND o.type != 'P'
547               AND c.name like @column_name
548               --	AND d.ss_dtype IN (111, 109, 38, 110)	/* Just *N types */
549               AND d.ss_dtype IN (111, 109, 38, 110, 68) /* Just *N types */
550               AND c.usertype < 100
551               AND m.type_name =
552               rtrim(substring(d.type_name,
553                       1 + isnull(d.aux,
554                           ascii(substring("III<<<MMMI<<A<A",
555                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
556                                   1)) - 60), 18))
557           UNION
558           SELECT /* All other types including user data types */
559               TABLE_CATALOG = DB_NAME(),
560               TABLE_SCHEMA = USER_NAME(o.uid),
561               TABLE_NAME = o.name,
562               COLUMN_NAME = c.name,
563               COLUMN_GUID = convert(varchar(36), null),
564               COLUMN_PROPID = convert(int, null),
565               ORDINAL_POSITION = convert(int, c.colid),
566               COLUMN_HASDEFAULT = convert(bit, c.cdefault & 1),
567               COLUMN_DEFAULT = convert(varchar(254), null),
568               /*CT: IsNullable 0x20 (c.status&8 * 4),
569               MaybeNullable 0x40 (c.status&8* 8) - always MaybeNullable if IsNullable
570               IsFixedLength 0x10 - yes
571               IsLong 0x80 - no
572               */
573               COLUMN_FLAGS = convert(int, c.status & 8) * 12 +
574               case when c.type in (37, 39, 155, 34, 35, 174) then 0
575                   when c.type in (47) and (convert(int, c.status & 8) = 1) then 0
576                   else 16 end +
577               case when c.type in (34, 35, 174) then 128 /*text, image and unitext type*/
578                   else 0 end,
579               usertype = t.usertype,
580               IS_NULLABLE = convert(bit, c.status & 8),
581               DATA_TYPE = convert(smallint, m.data_type),
582               TYPE_GUID = convert(varchar(36), null),
583               /*CT: only for char, binary and bit in oledb, see spec*/
584               CHARACTER_MAXIMUM_LENGTH = case
585                   when c.type in (135, 155)
586                   then
587                   (isnull(convert(int, c.prec),
588                       isnull(convert(int, d.data_precision),
589                           convert(int, c.length)))
590                   + isnull(d.aux, convert(int,
591                       ascii(substring("???AAAFFFCKFOLS",
592                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))) / 2
593                   when c.type in (47, 39, 45, 37, 35, 34, 50)
594                   then
595                   isnull(convert(int, c.prec),
596                       isnull(convert(int, d.data_precision),
597                           convert(int, c.length)))
598                   + isnull(d.aux, convert(int,
599                       ascii(substring("???AAAFFFCKFOLS",
600                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
601                   else
602                       convert(int, null)
603               end,
604   
605               /*
606               ** if the datatype is of type CHAR or BINARY
607               ** then set char_octet_length to the same value
608               ** assigned in the "prec" column.
609               **
610               ** The first part of the logic is:
611               **
612               **   if(c.type in (47, 39, 45, 37, 35, 34))
613               **       set char_octet_length = prec;
614               **   if (c.type in (135, 155)
615               **       set char_octet_length = prec * 2;
616               **   else
617               **       set char_octet_length = null;
618               */
619               CHARACTER_OCTET_LENGTH = case
620                   when c.type in (47, 39, 45, 37, 35, 34, 50)
621                   then /*same size as the CHARACTER_MAXIMUM_LENGTH */
622                   isnull(convert(int, c.prec),
623                       isnull(convert(int, d.data_precision),
624                           convert(int, c.length)))
625                   + isnull(d.aux, convert(int,
626                       ascii(substring("???AAAFFFCKFOLS",
627                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
628                   when c.type in (135, 155)
629                   then /* CHARACTER_MAXIMUM_LENGTH * 2 for the unichar, univarchar*/
630                   isnull(convert(int, c.prec),
631                       isnull(convert(int, d.data_precision),
632                           convert(int, c.length)))
633                   + isnull(d.aux, convert(int,
634                       ascii(substring("???AAAFFFCKFOLS",
635                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
636                   else
637                       convert(int, null)
638               end,
639               NUMERIC_PRECISION = case
640                   when c.type in (47, 39, 45, 37, 35, 34, 135, 155, 50, 58, 61, 123, 147, 174)
641                   then convert(smallint, null)
642                   else
643                       isnull(convert(smallint, c.prec),
644                           isnull(convert(smallint, d.data_precision),
645                               convert(smallint, c.length)))
646                       + isnull(d.aux, convert(smallint,
647                           ascii(substring("???AAAFFFCKFOLS",
648                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1)) - 60))
649               end,
650               NUMERIC_PRECISION_RADIX = d.numeric_radix,
651               TYPE_NAME =
652               case
653                   when convert(bit, (c.status & 0x80)) = 1 then
654                   rtrim(substring(d.type_name,
655                           1 + isnull(d.aux,
656                               ascii(substring('III<<<MMMI<<A<A',
657                                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
658                                       1)) - 60), 18)) + ' identity'
659                   else
660                       rtrim(substring(d.type_name,
661                               1 + isnull(d.aux,
662                                   ascii(substring('III<<<MMMI<<A<A',
663                                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
664                                           1)) - 60), 18))
665               end,
666               NUMERIC_SCALE = case
667                   when c.type in (63, 108, 55, 106) then
668                   isnull(convert(smallint, c.scale),
669                       convert(smallint, d.numeric_scale)) +
670                   convert(smallint, isnull(d.aux,
671                       ascii(substring("<<<<<<<<<<<<<<?",
672                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
673                               1)) - 60))
674                   else
675                       convert(smallint, null)
676               end,
677               DATETIME_PRECISION = case
678                   when c.type in (58, 61, 123, 147, 111) then
679                   isnull(convert(int, c.scale),
680                       convert(int, d.numeric_scale)) +
681                   convert(int, isnull(d.aux,
682                       ascii(substring("<<<<<<<<<<<<<<?",
683                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
684                               1)) - 60))
685                   else
686                       convert(int, null)
687               end,
688               CHARACTER_SET_CATALOG = null,
689               CHARACTER_SET_SCHEMA = null,
690               CHARACTER_SET_NAME = null,
691               COLLATION_CATALOG = null,
692               COLLATION_SCHEMA = null,
693               COLLATION_NAME = null,
694               DOMAIN_CATALOG = null,
695               DOMAIN_SCHEMA = null,
696               DOMAIN_NAME = null,
697               DESCRIPTION = null,
698               tds_type = c.type,
699               id = c.id,
700               col_len = c.length,
701               sys_type = t.type
702   
703           FROM
704               syscolumns c,
705               sysobjects o,
706               sybsystemprocs.dbo.spt_datatype_info d,
707               systypes t,
708               sybsystemprocs.dbo.spt_sybdrv m
709           WHERE
710               o.name like @table_name
711               AND user_name(o.uid) like @table_owner
712               AND o.id = c.id
713               /*
714               ** We use syscolumn.usertype instead of syscolumn.type
715               ** to do join with systypes.usertype. This is because
716               ** for a column which allows null, type stores its
717               ** Server internal datatype whereas usertype still
718               ** stores its user defintion datatype.  For an example,
719               ** a column of type 'decimal NULL', its usertype = 26,
720               ** representing decimal whereas its type = 106 
721               ** representing decimaln. nullable in the select list
722               ** already tells user whether the column allows null.
723               ** In the case of user defining datatype, this makes
724               ** more sense for the user.
725               */
726               AND c.usertype = t.usertype
727               /*
728               ** We need a equality join with 
729               ** sybsystemprocs.dbo.spt_datatype_info here so that
730               ** there is only one qualified row returned from 
731               ** sybsystemprocs.dbo.spt_datatype_info, thus avoiding
732               ** duplicates.
733               */
734               AND t.type = d.ss_dtype
735               AND c.name like @column_name
736               AND o.type != 'P'
737               AND c.name like @column_name
738               --	AND (d.ss_dtype NOT IN (111, 109, 38, 110) /* No *N types */
739               AND (d.ss_dtype NOT IN (111, 109, 38, 110, 68) /* No *N types */
740   
741                   OR c.usertype >= 100) /* User defined types */
742               AND m.type_name =
743               rtrim(substring(d.type_name,
744                       1 + isnull(d.aux,
745                           ascii(substring("III<<<MMMI<<A<A",
746                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length,
747                                   1)) - 60), 18))
748   
749           ORDER BY TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
750       end
751   
752   SelectClause:
753   
754       /*  usertype 3 (binary) , 80 (timestamp) */
755       UPDATE #results_table set COLUMN_FLAGS = 112 where usertype in (3) and IS_NULLABLE = 0
756       UPDATE #results_table set DATA_TYPE = 4 where DATA_TYPE = 5 and tds_type = 59
757       UPDATE #results_table set DATA_TYPE = 4 where DATA_TYPE = 5 and col_len = 4
758       UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 96 and sys_type = 47 and DATA_TYPE = 129 and not (IS_NULLABLE = 1 and @stripblanks = 1)
759       UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 96 and sys_type = 135 and DATA_TYPE = 130 and not (IS_NULLABLE = 1 and @stripblanks = 1)
760       UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 16 and tds_type = 62 and DATA_TYPE = 5
761       UPDATE #results_table set COLUMN_FLAGS = 112 where COLUMN_FLAGS = 16 and tds_type = 59 and DATA_TYPE = 4
762       if (@is_ado = 1)
763       begin
764           select TABLE_CATALOG,
765               TABLE_SCHEMA,
766               TABLE_NAME,
767               COLUMN_NAME,
768               COLUMN_GUID,
769               COLUMN_PROPID,
770               ORDINAL_POSITION = convert(int, row_id),
771               COLUMN_HASDEFAULT,
772               COLUMN_DEFAULT,
773               COLUMN_FLAGS,
774               -- 	USERTYPE = usertype,
775               IS_NULLABLE,
776               DATA_TYPE,
777               --TYPE_NAME,
778               TYPE_GUID,
779               CHARACTER_MAXIMUM_LENGTH,
780               CHARACTER_OCTET_LENGTH,
781               NUMERIC_PRECISION,
782               --	NUMERIC_PRECISION_RADIX,
783               NUMERIC_SCALE,
784               DATETIME_PRECISION,
785               CHARACTER_SET_CATALOG,
786               CHARACTER_SET_SCHEMA,
787               CHARACTER_SET_NAME,
788               COLLATION_CATALOG,
789               COLLATION_SCHEMA,
790               COLLATION_NAME,
791               DOMAIN_CATALOG,
792               DOMAIN_SCHEMA,
793               DOMAIN_NAME,
794               DESCRIPTION
795           -- 	TDSTYPE = tds_type,
796           -- 	COLID = id,
797           --	COLLEN = col_len
798   
799           FROM
800               #results_table
801           order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
802   
803       end
804       else
805       if (@is_ado = 2)
806       begin
807           select TABLE_CATALOG,
808               TABLE_SCHEMA,
809               TABLE_NAME,
810               COLUMN_NAME,
811               COLUMN_GUID,
812               COLUMN_PROPID,
813               ORDINAL_POSITION = convert(int, row_id),
814               COLUMN_HASDEFAULT,
815               COLUMN_DEFAULT,
816               COLUMN_FLAGS,
817               -- 	USERTYPE = usertype,
818               IS_NULLABLE,
819               --DATA_TYPE ,
820               TYPE_NAME,
821               TYPE_GUID,
822               CHARACTER_MAXIMUM_LENGTH,
823               CHARACTER_OCTET_LENGTH,
824               NUMERIC_PRECISION,
825               NUMERIC_PRECISION_RADIX,
826               NUMERIC_SCALE,
827               DATETIME_PRECISION,
828               CHARACTER_SET_CATALOG,
829               CHARACTER_SET_SCHEMA,
830               CHARACTER_SET_NAME,
831               COLLATION_CATALOG,
832               COLLATION_SCHEMA,
833               COLLATION_NAME,
834               DOMAIN_CATALOG,
835               DOMAIN_SCHEMA,
836               DOMAIN_NAME,
837               DESCRIPTION
838           -- 	TDSTYPE = tds_type,
839           -- 	COLID = id,
840           --	COLLEN = col_len
841   
842           FROM
843               #results_table
844           order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION
845       end
846   
847   
848       if (@startedInTransaction = 1)
849           rollback transaction oledb_keep_temptable_tx
850   
851       return (0)
852   


exec sp_procxmode 'sp_oledb_columns', 'AnyMode'
go

Grant Execute on sp_oledb_columns to public
go
RESULT SETS
sp_oledb_columns_rset_002
sp_oledb_columns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 231
 QJWI 5 Join or Sarg Without Index 236
 QJWI 5 Join or Sarg Without Index 420
 QJWI 5 Join or Sarg Without Index 426
 QJWI 5 Join or Sarg Without Index 545
 QJWI 5 Join or Sarg Without Index 551
 QJWI 5 Join or Sarg Without Index 734
 QJWI 5 Join or Sarg Without Index 742
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 180
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 235
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 425
 QTYP 4 Comparison type mismatch Comparison type mismatch: tinyint vs int 494
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 550
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 741
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 756
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 757
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 758
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 759
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 760
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 761
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 215
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 216
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 216
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 385
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 397
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 398
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 398
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 517
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 530
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 530
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 699
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 712
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 712
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 163
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 338
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 477
 MBLI 3 Integer Value of Binary Literal is Platform Dependant 653
 MGTP 3 Grant to public sybsystemprocs..sp_oledb_columns  
 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..systypes  
 MNER 3 No Error Check should check @@error after insert 129
 MNER 3 No Error Check should check @@error after insert 444
 MNER 3 No Error Check should check @@error after update 755
 MNER 3 No Error Check should check @@error after update 756
 MNER 3 No Error Check should check @@error after update 757
 MNER 3 No Error Check should check @@error after update 758
 MNER 3 No Error Check should check @@error after update 759
 MNER 3 No Error Check should check @@error after update 760
 MNER 3 No Error Check should check @@error after update 761
 MUCO 3 Useless Code Useless Brackets in create proc 25
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 762
 MUCO 3 Useless Code Useless Brackets 805
 MUCO 3 Useless Code Useless Brackets 848
 MUCO 3 Useless Code Useless Brackets 851
 MUTI 3 Update temptable with identity - 12.5.4 Regression 755
 MUTI 3 Update temptable with identity - 12.5.4 Regression 756
 MUTI 3 Update temptable with identity - 12.5.4 Regression 757
 MUTI 3 Update temptable with identity - 12.5.4 Regression 758
 MUTI 3 Update temptable with identity - 12.5.4 Regression 759
 MUTI 3 Update temptable with identity - 12.5.4 Regression 760
 MUTI 3 Update temptable with identity - 12.5.4 Regression 761
 QCRS 3 Conditional Result Set 764
 QCRS 3 Conditional Result Set 807
 QISO 3 Set isolation level 43
 QNAJ 3 Not using ANSI Inner Join 208
 QNAJ 3 Not using ANSI Inner Join 390
 QNAJ 3 Not using ANSI Inner Join 521
 QNAJ 3 Not using ANSI Inner Join 703
 QPNC 3 No column in condition 758
 QPNC 3 No column in condition 759
 QRPR 3 Repeated predicate c.name like @column_name 737
 QUNI 3 Check Use of 'union' vs 'union all' 130
 QUNI 3 Check Use of 'union' vs 'union all' 445
 VNRD 3 Variable is not read @char_bin_types 49
 MRST 2 Result Set Marker 764
 MRST 2 Result Set Marker 807
 MTR1 2 Metrics: Comments Ratio Comments: 27% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 15dec - 1exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 178 25

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#results_table (1) 
writes table sybsystemprocs..sp_oledb_columns_rset_002 
reads table sybsystemprocs..systypes  
writes table sybsystemprocs..sp_oledb_columns_rset_001 
reads table sybsystemprocs..spt_datatype_info  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..spt_sybdrv