DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_getversioncolumns  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_odbc_getversioncolumns', 'AnyMode'
go

Grant Execute on sp_odbc_getversioncolumns to public
go
RESULT SETS
sp_odbc_getversioncolumns_rset_003
sp_odbc_getversioncolumns_rset_002
sp_odbc_getversioncolumns_rset_001

DEFECTS
 QJWI 5 Join or Sarg Without Index 103
 QJWI 5 Join or Sarg Without Index 158
 QJWI 5 Join or Sarg Without Index 159
 QJWI 5 Join or Sarg Without Index 217
 QJWI 5 Join or Sarg Without Index 218
 QCAS 4 Cartesian product with single row between tables sybsystemprocs..systypes t and [sybsystemprocs..spt_datatype_info d, sybsystemprocs..... 72
 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]
212
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 104
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 105
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 172
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 215
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 216
 QTYP 4 Comparison type mismatch smallint = int 216
 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 102
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 120
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 156
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 171
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 211
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 213
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 213
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 214
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 214
 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 15
 MUCO 3 Useless Code Useless Brackets 38
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 107
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 220
 QCRS 3 Conditional Result Set 72
 QCRS 3 Conditional Result Set 122
 QISO 3 Set isolation level 32
 QNAJ 3 Not using ANSI Inner Join 99
 QNAO 3 Not using ANSI Outer Join 151
 QNAO 3 Not using ANSI Outer Join 203
 QNUA 3 Should use Alias: Column indid should use alias x 176
 QNUA 3 Should use Alias: Column keycnt should use alias x 215
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
102
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
120
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
156
 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
 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]
214
 QTJ1 3 Table only appears in inner join clause 154
 QTJ1 3 Table only appears in inner join clause 207
 VUNU 3 Variable is not used @version 25
 MRST 2 Result Set Marker 72
 MRST 2 Result Set Marker 122
 MRST 2 Result Set Marker 174
 MSUB 2 Subquery Marker 119
 MSUB 2 Subquery Marker 166
 MTR1 2 Metrics: Comments Ratio Comments: 18% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 13dec - 6exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 76 15
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, sdi=sybsystemprocs..spt_datatype_info} 0 72

DATA PROPAGATION detailed
ColumnWritten To
@table_namesp_odbc_getversioncolumns_rset_001.COLUMN_NAME
@table_ownersp_odbc_getversioncolumns_rset_001.COLUMN_NAME

DEPENDENCIES
PROCS AND TABLES USED
writes table sybsystemprocs..sp_odbc_getversioncolumns_rset_003 
reads table sybsystemprocs..systypes  
reads table sybsystemprocs..syscolumns  
reads table sybsystemprocs..spt_datatype_info  
writes table sybsystemprocs..sp_odbc_getversioncolumns_rset_001 
writes table sybsystemprocs..sp_odbc_getversioncolumns_rset_002 
reads table sybsystemprocs..sysindexes