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

1     
2     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
3     /*	10.0	1.0	9 JUL 93	sproc/src/special_columns */
4     
5     /* 
6     ** Messages for "sp_odbc_getversioncolumns"
7     **
8     ** 17863, "There is no table named %1! in the current database."
9     ** 18039, "Table qualifier must be name of current database."
10    ** 18042, "Illegal value for 'col_type' argument. Legal values are 'V' or 'R'."
11    **
12    */
13    
14    create procedure sp_odbc_getversioncolumns(
15        @table_name varchar(771),
16        @table_owner varchar(32) = null,
17        @table_qualifier varchar(32) = null,
18        @col_type char(1) = 'R')
19    as
20        declare @indid int
21        declare @table_id int
22        declare @dbname varchar(255)
23        declare @full_table_name varchar(1543)
24        declare @version varchar(32)
25    
26        if @@trancount = 0
27        begin
28            set chained off
29        end
30    
31        set transaction isolation level 1
32    
33        /* get database name */
34        select @dbname = db_name()
35    
36        /* we don't want a temp table unless we're in tempdb */
37        if (@table_name like "#%" and @dbname != db_name(tempdb_id()))
38        -- 	if (@table_name like "#%" and @dbname != 'tempdb')		-- 12.0
39        -- 	if @table_name like "#%" and @dbname != db_name(tempdb_id())	-- 12.5
40    
41        begin
42            /* 17863, "There is no table named %1! in the current database." */
43            raiserror 17863, @table_name
44            return (1)
45        end
46    
47        if @table_qualifier is not null
48        begin
49            if @dbname != @table_qualifier
50            begin
51                /* 18039, "Table qualifier must be name of current database." */
52                raiserror 18039
53                return (1)
54            end
55        end
56    
57        if @table_owner is null
58        begin /* if unqualified table name */
59            select @full_table_name = @table_name
60        end
61        else
62        begin /* qualified table name */
63            select @full_table_name = @table_owner + '.' + @table_name
64        end
65    
66        /* get object ID */
67        select @table_id = object_id(@full_table_name)
68    
69        if @col_type = 'V'
70        begin /* if ROWVER, just run that query */
71            select
72                SCOPE = convert(smallint, 0),
73                COLUMN_NAME = c.name,
74                DATA_TYPE = d.data_type + convert(smallint,
75                isnull(d.aux,
76                    ascii(substring("666AAA@@@CB??GG",
77                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
78                    - 60)),
79                TYPE_NAME = t.name,
80                COLUMN_SIZE = isnull(d.data_precision,
81                    convert(int, c.length))
82                + isnull(d.aux, convert(int,
83                    ascii(substring("???AAAFFFCKFOLS",
84                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
85                    - 60)),
86                BUFFER_LENGTH = isnull(d.length, convert(int, c.length))
87                + convert(int,
88                isnull(d.aux,
89                    ascii(substring("AAA<BB<DDDHJSPP",
90                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
91                    - 64)),
92                DECIMAL_DIGITS = d.numeric_scale + convert(smallint,
93                isnull(d.aux,
94                    ascii(substring("<<<<<<<<<<<<<<?",
95                            2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
96                    - 60)),
97                PSEUDO_COLUMN = convert(smallint, 1)
98            from
99                systypes t, syscolumns c, sybsystemprocs.dbo.spt_datatype_info d
100           where
101               c.id = @table_id
102               and c.type = d.ss_dtype
103               and c.usertype = 80 /* TIMESTAMP */
104               and t.usertype = 80 /* TIMESTAMP */
105   
106           return (0)
107       end
108   
109       if @col_type != 'R'
110       begin
111           /* 18042, "Illegal value for 'col_type' argument. Legal values are 'V' or 'R'." */
112   
113           raiserror 18042
114           return (1)
115       end
116   
117       /* An identity column is the most optimal unique identifier */
118       if exists (select colid from syscolumns
119               where id = @table_id and (status & 128) = 128)
120       begin
121           select
122               SCOPE = convert(smallint, 0),
123               COLUMN_NAME = c.name,
124               DATA_TYPE = d.data_type + convert(smallint,
125               isnull(d.aux,
126                   ascii(substring("666AAA@@@CB??GG",
127                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
128                   - 60)),
129               TYPE_NAME = rtrim(substring(d.type_name,
130                       1 + isnull(d.aux,
131                           ascii(substring("III<<<MMMI<<A<A",
132                                   2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
133                           - 60), 18)),
134               COLUMN_SIZE = isnull(d.data_precision, convert(int, c.length))
135               + isnull(d.aux, convert(int,
136                   ascii(substring("???AAAFFFCKFOLS",
137                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
138                   - 60)),
139               BUFFER_LENGTH = isnull(d.length, convert(int, c.length))
140               + convert(int, isnull(d.aux,
141                   ascii(substring("AAA<BB<DDDHJSPP",
142                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
143                   - 64)),
144               DECIMAL_DIGITS = d.numeric_scale + convert(smallint,
145               isnull(d.aux,
146                   ascii(substring("<<<<<<<<<<<<<<?",
147                           2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c.length, 1))
148                   - 60)),
149               PSEUDO_COLUMN = convert(smallint, 1)
150           from
151               syscolumns c,
152               sybsystemprocs.dbo.spt_datatype_info d,
153               systypes t
154           where
155               c.id = @table_id
156               and (c.status & 128) = 128
157               and c.type = d.ss_dtype
158               and c.usertype *= t.usertype
159   
160           return (0)
161       end
162   
163       /* ROWID, now find the id of the 'best' index for this table */
164   
165       select @indid = (
166               select min(indid)
167               from sysindexes
168               where
169                   status & 2 = 2 /* if unique index */
170                   and id = @table_id
171                   and indid > 0) /* eliminate table row */
172   
173       select
174           SCOPE = convert(smallint, 0),
175           COLUMN_NAME = index_col(@full_table_name, indid, c.colid),
176           DATA_TYPE = d.data_type + convert(smallint,
177           isnull(d.aux,
178               ascii(substring("666AAA@@@CB??GG",
179                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
180               - 60)),
181           TYPE_NAME = rtrim(substring(d.type_name,
182                   1 + isnull(d.aux,
183                       ascii(substring("III<<<MMMI<<A<A",
184                               2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
185                       - 60), 18)),
186           COLUMN_SIZE = isnull(d.data_precision, convert(int, c2.length))
187           + isnull(d.aux, convert(int,
188               ascii(substring("???AAAFFFCKFOLS",
189                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
190               - 60)),
191           BUFFER_LENGTH = isnull(d.length, convert(int, c2.length))
192           + convert(int, isnull(d.aux,
193               ascii(substring("AAA<BB<DDDHJSPP",
194                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
195               - 64)),
196           DECIMAL_DIGITS = d.numeric_scale + convert(smallint,
197           isnull(d.aux,
198               ascii(substring("<<<<<<<<<<<<<<?",
199                       2 * (d.ss_dtype % 35 + 1) + 2 - 8 / c2.length, 1))
200               - 60)),
201           PSEUDO_COLUMN = convert(smallint, 1)
202       from
203           sysindexes x,
204           syscolumns c,
205           sybsystemprocs.dbo.spt_datatype_info d,
206           systypes t,
207           syscolumns c2 /* self-join to generate list of index
208       ** columns and to extract datatype names */
209       where
210           x.id = @table_id
211           and c2.name = index_col(@full_table_name, @indid, c.colid)
212           and c2.id = x.id
213           and c.id = x.id
214           and c.colid < keycnt + (x.status & 16) / 16
215           and x.indid = @indid
216           and c2.type = d.ss_dtype
217           and c2.usertype *= t.usertype
218   
219       return (0)
220   

exec sp_procxmode 'sp_odbc_getversioncolumns', 'AnyMode'
go

Grant Execute on sp_odbc_getversioncolumns to public
go

DEFECTS
QJWI 5 Join or Sarg Without Index	102
QJWI 5 Join or Sarg Without Index	157
QJWI 5 Join or Sarg Without Index	158
QJWI 5 Join or Sarg Without Index	216
QJWI 5 Join or Sarg Without Index	217
QCAS 4 Cartesian product with single row between tables sybsystemprocs..systypes t and [sybsystemprocs..spt_datatype_info d, sybsystemprocs.....	71
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {colid} Uncovered: [id, number]	211
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	103
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	104
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	171
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	214
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	215
QTYP 4 Comparison type mismatch smallint = int	215
TNOI 4 Table with no index sybsystemprocs..spt_datatype_info	sybsystemprocs..spt_datatype_info
MGTP 3 Grant to public sybsystemprocs..sp_odbc_getversioncolumns
MGTP 3 Grant to public sybsystemprocs..spt_datatype_info
MGTP 3 Grant to public sybsystemprocs..syscolumns
MGTP 3 Grant to public sybsystemprocs..sysindexes
MGTP 3 Grant to public sybsystemprocs..systypes
MUCO 3 Useless Code Useless Brackets in create proc	14
MUCO 3 Useless Code Useless Brackets	37
MUCO 3 Useless Code Useless Brackets	44
MUCO 3 Useless Code Useless Brackets	53
MUCO 3 Useless Code Useless Brackets	106
MUCO 3 Useless Code Useless Brackets	114
MUCO 3 Useless Code Useless Brackets	160
MUCO 3 Useless Code Useless Brackets	219
QCRS 3 Conditional Result Set	71
QCRS 3 Conditional Result Set	121
QISO 3 Set isolation level	31
QNAJ 3 Not using ANSI Inner Join	98
QNAO 3 Not using ANSI Outer Join	150
QNAO 3 Not using ANSI Outer Join	202
QNUA 3 Should use Alias: Column indid should use alias x	175
QNUA 3 Should use Alias: Column keycnt should use alias x	214
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	101
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	119
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	155
QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id} Uncovered: [number, colid]	212
QPRI 3 Join or Sarg with Rooted Partial Index Use JOIN Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id} Uncovered: [number, colid]	213
QTJ1 3 Table only appears in inner join clause	153
QTJ1 3 Table only appears in inner join clause	206
VUNU 3 Variable is not used @version	24
MRST 2 Result Set Marker	71
MRST 2 Result Set Marker	121
MRST 2 Result Set Marker	173
MSUB 2 Subquery Marker	118
MSUB 2 Subquery Marker	165
MTR1 2 Metrics: Comments Ratio Comments: 18%	14
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 8 = 12dec - 6exi + 2	14
MTR3 2 Metrics: Query Complexity Complexity: 76	14
PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, sdi=sybsystemprocs..spt_datatype_info} 0	71

DEPENDENCIES

PROCS AND TABLES USED reads table sybsystemprocs..sysindexes   reads table sybsystemprocs..systypes   reads table sybsystemprocs..syscolumns   reads table sybsystemprocs..spt_datatype_info