DatabaseProcApplicationCreatedLinks
sybsystemprocssp_datatype_info  31 Aug 14Defects Dependencies

1     
2     
3     /* 
4     ** Sccsid = "%Z% generic/sproc/%M% %I% %G%"
5     **
6     ** History:
7     **	10.0	steven	1.1	07/13/93	sproc/src/datatype_info
8     **						Ported from MS catalog SP's
9     **
10    ** Implementation Notes:
11    ** 	The messiness of 'sp_data_type_info' was to get around the
12    ** problem of returning the correct lengths for user defined types.  The
13    ** join on the type name ensures all user defined types are returned, but
14    ** this puts a null in the data_type column.  By forcing an embedded
15    ** select and correlating it with the current row in systypes, we get the
16    ** correct data_type mapping even for user defined types.  
17    */
18    
19    create procedure sp_datatype_info
20        @data_type int = 0 /* Provide datatype_info for type # */
21    as
22        if (select @data_type) = 0
23        begin
24            select /* Real SQL Server data types */
25                type_name =
26                case
27                    when t.usertype in (44, 45, 46)
28                    then "unsigned " + substring(t.name,
29                        charindex("u", t.name) + 1,
30                        charindex("t", t.name))
31                    else
32                        t.name
33                end,
34                d.data_type,
35                "precison" = isnull(d.data_precision,
36                    convert(int, t.length)),
37                d.literal_prefix,
38                d.literal_suffix,
39                e.create_params,
40                d.nullable,
41                d.case_sensitive,
42                d.searchable,
43                d.unsigned_attribute,
44                d.money,
45                d.auto_increment,
46                d.local_type_name,
47                d.minimum_scale,
48                d.maximum_scale,
49                d.sql_data_type,
50                d.sql_datetime_sub,
51                d.num_prec_radix,
52                d.interval_precision
53            from sybsystemprocs.dbo.spt_datatype_info d,
54                sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
55            where
56                d.ss_dtype = t.type
57                and t.usertype *= e.user_type
58                /* restrict results to "real" datatypes */
59                and t.name not in ("nchar", "nvarchar",
60                    "sysname", "longsysname", "timestamp",
61                    "datetimn", "floatn", "intn", "moneyn",
62                    "uintn")
63                and t.usertype < 100 /* No user defined types */
64            UNION
65            select /* SQL Server user data types */
66                type_name = t.name,
67                d.data_type,
68                "precison" = isnull(d.data_precision,
69                    convert(int, t.length)),
70                d.literal_prefix,
71                d.literal_suffix,
72                e.create_params,
73                d.nullable,
74                d.case_sensitive,
75                d.searchable,
76                d.unsigned_attribute,
77                d.money,
78                d.auto_increment,
79                t.name,
80                d.minimum_scale,
81                d.maximum_scale,
82                d.sql_data_type,
83                d.sql_datetime_sub,
84                d.num_prec_radix,
85                d.interval_precision
86            from sybsystemprocs.dbo.spt_datatype_info d,
87                sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
88            where
89                d.ss_dtype = t.type
90                and t.usertype *= e.user_type
91                /* 
92                ** Restrict to user defined types (value > 100)
93                ** and Sybase user defined types (listed)
94                */
95                and (t.name in ("nchar", "nvarchar",
96                        "sysname", "longsysname", "timestamp")
97                    or t.usertype >= 100) /* User defined types */
98            order by d.data_type, type_name
99        end
100       else
101       begin
102           select /* Real SQL Server data types */
103               type_name =
104               case
105                   when t.usertype in (44, 45, 46)
106                   then "unsigned " + substring(t.name,
107                       charindex("u", t.name) + 1,
108                       charindex("t", t.name))
109                   else
110                       t.name
111               end,
112               d.data_type,
113               "precison" = isnull(d.data_precision,
114                   convert(int, t.length)),
115               d.literal_prefix,
116               d.literal_suffix,
117               e.create_params,
118               d.nullable,
119               d.case_sensitive,
120               d.searchable,
121               d.unsigned_attribute,
122               d.money,
123               d.auto_increment,
124               d.local_type_name,
125               d.sql_data_type,
126               d.sql_datetime_sub,
127               d.num_prec_radix,
128               d.interval_precision
129           from sybsystemprocs.dbo.spt_datatype_info d,
130               sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
131           where
132               data_type = @data_type
133               and d.ss_dtype = t.type
134               and t.usertype *= e.user_type
135               /* restrict results to "real" datatypes */
136               and t.name not in ("nchar", "nvarchar",
137                   "sysname", "longsysname", "timestamp",
138                   "datetimn", "floatn", "intn", "moneyn",
139                   "uintn")
140               and t.usertype < 100 /* No user defined types */
141           UNION
142           select /* SQL Server and user data types */
143               type_name = t.name,
144               d.data_type,
145               "precison" = isnull(d.data_precision,
146                   convert(int, t.length)),
147               d.literal_prefix,
148               d.literal_suffix,
149               e.create_params,
150               d.nullable,
151               d.case_sensitive,
152               d.searchable,
153               d.unsigned_attribute,
154               d.money,
155               d.auto_increment,
156               t.name,
157               d.sql_data_type,
158               d.sql_datetime_sub,
159               d.num_prec_radix,
160               d.interval_precision
161           from sybsystemprocs.dbo.spt_datatype_info d,
162               sybsystemprocs.dbo.spt_datatype_info_ext e, systypes t
163           where
164               data_type = @data_type
165               and d.ss_dtype = t.type
166               and t.usertype *= e.user_type
167               /* 
168               ** Restrict to user defined types (value > 100)
169               ** and Sybase user defined types (listed)
170               */
171               and (t.name in ("nchar", "nvarchar",
172                       "sysname", "longsysname", "timestamp")
173                   or t.usertype >= 100) /* User defined types */
174           order by type_name
175       end
176       return (0)
177   


exec sp_procxmode 'sp_datatype_info', 'AnyMode'
go

Grant Execute on sp_datatype_info to public
go
RESULT SETS
sp_datatype_info_rset_002
sp_datatype_info_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 56
 QJWI 5 Join or Sarg Without Index 57
 QJWI 5 Join or Sarg Without Index 89
 QJWI 5 Join or Sarg Without Index 90
 QJWI 5 Join or Sarg Without Index 133
 QJWI 5 Join or Sarg Without Index 134
 QJWI 5 Join or Sarg Without Index 165
 QJWI 5 Join or Sarg Without Index 166
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 63
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 97
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 132
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 140
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 164
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 173
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info sybsystemprocs..spt_datatype_info
 TNOI 4 Table with no index sybsystemprocs..spt_datatype_info_ext sybsystemprocs..spt_datatype_info_ext
 MGTP 3 Grant to public sybsystemprocs..sp_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info  
 MGTP 3 Grant to public sybsystemprocs..spt_datatype_info_ext  
 MGTP 3 Grant to public sybsystemprocs..systypes  
 MUCO 3 Useless Code Useless Brackets 176
 QCRS 3 Conditional Result Set 24
 QCRS 3 Conditional Result Set 102
 QNAO 3 Not using ANSI Outer Join 53
 QNAO 3 Not using ANSI Outer Join 86
 QNAO 3 Not using ANSI Outer Join 129
 QNAO 3 Not using ANSI Outer Join 161
 QNUA 3 Should use Alias: Column data_type should use alias d 132
 QNUA 3 Should use Alias: Column data_type should use alias d 164
 QUNI 3 Check Use of 'union' vs 'union all' 24
 QUNI 3 Check Use of 'union' vs 'union all' 102
 MRST 2 Result Set Marker 24
 MRST 2 Result Set Marker 102
 MTR1 2 Metrics: Comments Ratio Comments: 21% 19
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 1dec - 1exi + 2 19
 MTR3 2 Metrics: Query Complexity Complexity: 38 19

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..spt_datatype_info  
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..spt_datatype_info_ext