DatabaseProcApplicationCreatedLinks
sybsystemprocssp_odbc_getindexinfo  14 déc. 14Defects Propagation Dependencies

1     
2     
3     
4     /* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
5     
6     /*
7     ** Messages for "sp_odbc_getindexinfo"          18039
8     **
9     ** 18039, "Table qualifier must be name of current database."
10    ** 18040, "Catalog procedure '%1!' can not be run in a transaction.
11    **
12    */
13    
14    /*
15    ** Sp_statistics returns statistics for the given table, passed as first 
16    ** argument. A row is returned for the table and then for each index found
17    ** in sysindexes, starting with lowest value index id in sysindexes and 
18    ** proceeding through the highest value index.  
19    **
20    ** Returned rows consist of the columns:
21    ** table qualifier (database name), table owner, table name from sysobjects, 
22    ** non_unique (0/1), index qualifier (same as table name), 
23    ** index name from sysindexes, type (SQL_INDEX_CLUSTERED/SQL_INDEX_OTHER), 
24    ** sequence in index, column name, collation, table cardinality (row count), 
25    ** and number of pages used by table (doampg).
26    */
27    
28    CREATE OR REPLACE PROCEDURE sp_odbc_getindexinfo(
29        @table_name varchar(771),
30        @table_owner varchar(32) = null,
31        @table_qualifier varchar(32) = null,
32        @index_name varchar(771) = '%',
33        @is_unique char(1) = 'N')
34    as
35        declare @indid int
36        declare @lastindid int
37        declare @full_table_name varchar(1543)
38        declare @startedInTransaction bit
39    
40        if (@@trancount > 0)
41            select @startedInTransaction = 1
42        else
43            select @startedInTransaction = 0
44    
45        /*
46        ** Verify table qualifier is name of current database.
47        */
48        if @table_qualifier is not null
49        begin
50            if db_name() != @table_qualifier
51            begin /* If qualifier doesn't match current database */
52                /*
53                ** 18039, "Table qualifier must be name of current database."
54                */
55                raiserror 18039
56                return (1)
57            end
58        end
59    
60        if @@trancount = 0
61        begin
62            set chained off
63        end
64    
65        set transaction isolation level 1
66        if (@startedInTransaction = 1)
67            save transaction odbc_keep_temptable_tx
68    
69    
70        create table #TmpIndex(
71            TABLE_CAT varchar(32),
72            TABLE_SCHEM varchar(32),
73            TABLE_NAME varchar(255),
74            INDEX_QUALIFIER varchar(255) null,
75            INDEX_NAME varchar(255) null,
76            NON_UNIQUE smallint null,
77            TYPE smallint,
78            ORDINAL_POSITION smallint null,
79            COLUMN_NAME varchar(255) null,
80            ASC_OR_DESC char(1) null,
81            index_id int null,
82            CARDINALITY int null,
83            PAGES int null,
84            FILTER_CONDITION varchar(32) null,
85            status smallint,
86            status2 smallint)
87    
88        /*
89        ** Fully qualify table name.
90        */
91        if @table_owner is null
92        begin /* If unqualified table name */
93            select @full_table_name = @table_name
94        end
95        else
96        begin /* Qualified table name */
97            select @full_table_name = @table_owner + '.' + @table_name
98        end
99    
100       /*
101       ** Start at lowest index id, while loop through indexes. 
102       ** Create a row in #TmpIndex for every column in sysindexes, each is
103       ** followed by an row in #TmpIndex with table statistics for the preceding
104       ** index.
105       */
106       select @indid = min(indid)
107       from sysindexes
108       where id = object_id(@full_table_name)
109           and indid > 0
110           and indid < 255
111   
112       while @indid != NULL
113       begin
114           insert #TmpIndex /* Add all columns that are in index */
115           select
116               db_name(), /* table_qualifier */
117               user_name(o.uid), /* table_owner	   */
118               o.name, /* table_name	   */
119               o.name, /* index_qualifier */
120               x.name, /* index_name	   */
121               0, /* non_unique	   */
122               1, /* SQL_INDEX_CLUSTERED */
123               colid, /* seq_in_index	   */
124               INDEX_COL(@full_table_name, indid, colid), /* column_name	   */
125               index_colorder(@full_table_name,
126                   indid, colid), /* collation	   */
127               @indid, /* index_id 	   */
128               --	rowcnt(x.doampg),	/* cardinality	   */
129               row_count(db_id(), x.id), /* cardinality	*/
130               --	data_pgs(x.id,doampg),	/* pages	   */
131               data_pages(db_id(), x.id,
132                   case
133                       when x.indid = 1
134                       then 0
135                       else x.indid
136                   end), /* pages	   */
137               null, /* Filter condition not available */
138               /* in SQL Server*/
139               x.status, /* status	   */
140               x.status2 /* status2	   */
141           from sysindexes x, syscolumns c, sysobjects o
142           where x.id = object_id(@full_table_name)
143               and x.id = o.id
144               and x.id = c.id
145               and c.colid < keycnt + (x.status & 16) / 16
146               and x.indid = @indid
147   
148           /*
149           ** Save last index and increase index id to next higher value.
150           */
151           select @lastindid = @indid
152           select @indid = NULL
153   
154           select @indid = min(indid)
155           from sysindexes
156           where id = object_id(@full_table_name)
157               and indid > @lastindid
158               and indid < 255
159       end
160   
161       update #TmpIndex
162       set NON_UNIQUE = 1
163       where status & 2 != 2 /* If non-unique index */
164   
165       update #TmpIndex
166       set
167           TYPE = 3, /* SQL_INDEX_OTHER */
168           CARDINALITY = NULL,
169           PAGES = NULL
170       where index_id > 1 /* If non-clustered index */
171   
172       update #TmpIndex
173       set TYPE = 1 /* SQL_INDEX_CLUSTERED */
174       where
175           status2 & 512 = 512 /* if placement index */
176       /* 
177       ** Now add row with table statistics 
178       */
179       insert #TmpIndex
180       select
181           db_name(), /* table_qualifier */
182           user_name(o.uid), /* table_owner	   */
183           o.name, /* table_name	   */
184           null, /* index_qualifier */
185           null, /* index_name	   */
186           null, /* non_unique	   */
187           0, /* SQL_table_STAT  */
188           null, /* seq_in_index	*/
189           null, /* column_name	   */
190           null, /* collation	   */
191           0, /* index_id 	   */
192           --	rowcnt(x.doampg),	/* cardinality	   */
193           row_count(db_id(), x.id), /* cardinality	*/
194           --	data_pgs(x.id,doampg),	/* pages	   */
195           data_pages(db_id(), x.id,
196               case
197                   when x.indid = 1
198                   then 0
199                   else x.indid
200               end), /* pages	   */
201           null, /* Filter condition not available */
202           /* in SQL Server*/
203           0, /* status	   */
204           0 /* status2	   */
205       from sysindexes x, sysobjects o
206       where o.id = object_id(@full_table_name)
207           and x.id = o.id
208           and (x.indid = 0 or x.indid = 1)
209       /*  
210       ** If there are no indexes
211       ** then table stats are in a row with indid = 0
212       */
213   
214       if @is_unique != 'Y'
215       begin
216           /* If all indexes desired */
217           select
218               TABLE_CAT,
219               TABLE_SCHEM,
220               TABLE_NAME,
221               NON_UNIQUE,
222               INDEX_QUALIFIER,
223               INDEX_NAME,
224               TYPE,
225               ORDINAL_POSITION,
226               COLUMN_NAME,
227               ASC_OR_DESC,
228               CARDINALITY,
229               PAGES,
230               FILTER_CONDITION
231           from #TmpIndex
232           where INDEX_NAME like @index_name /* If matching name */
233               or INDEX_NAME is null /* If SQL_table_STAT row */
234           order by NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION
235       end
236       else
237       begin
238           /* else only unique indexes desired */
239           select
240               TABLE_CAT,
241               TABLE_SCHEM,
242               TABLE_NAME,
243               NON_UNIQUE,
244               INDEX_QUALIFIER,
245               INDEX_NAME,
246               TYPE,
247               ORDINAL_POSITION,
248               COLUMN_NAME,
249               ASC_OR_DESC,
250               CARDINALITY,
251               PAGES,
252               FILTER_CONDITION
253           from #TmpIndex
254           where (NON_UNIQUE = 0 /* If unique */
255                   or NON_UNIQUE is NULL) /* If SQL_table_STAT row */
256               and (INDEX_NAME like @index_name /* If matching name */
257                   or INDEX_NAME is NULL) /* If SQL_table_STAT row */
258           order by NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION
259   
260       end
261   
262       drop table #TmpIndex
263       if (@startedInTransaction = 1)
264           rollback transaction odbc_keep_temptable_tx
265   
266       return (0)
267   


exec sp_procxmode 'sp_odbc_getindexinfo', 'AnyMode'
go

Grant Execute on sp_odbc_getindexinfo to public
go
RESULT SETS
sp_odbc_getindexinfo_rset_002
sp_odbc_getindexinfo_rset_001

DEFECTS
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
208
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 109
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 110
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 145
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 146
 QTYP 4 Comparison type mismatch smallint = int 146
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch smallint = int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 158
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 197
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 208
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 254
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 108
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 129
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 131
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 142
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 143
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 143
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 144
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 144
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 156
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 193
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 195
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 206
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 207
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 207
 MGTP 3 Grant to public sybsystemprocs..sp_odbc_getindexinfo  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNAC 3 Not using ANSI 'is null' 112
 MNER 3 No Error Check should check @@error after insert 114
 MNER 3 No Error Check should check @@error after update 161
 MNER 3 No Error Check should check @@error after update 165
 MNER 3 No Error Check should check @@error after update 172
 MNER 3 No Error Check should check @@error after insert 179
 MUCO 3 Useless Code Useless Brackets in create proc 28
 MUCO 3 Useless Code Useless Brackets 40
 MUCO 3 Useless Code Useless Brackets 56
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 266
 MUIN 3 Column created using implicit nullability 70
 QCRS 3 Conditional Result Set 217
 QCRS 3 Conditional Result Set 239
 QISO 3 Set isolation level 65
 QNAJ 3 Not using ANSI Inner Join 141
 QNAJ 3 Not using ANSI Inner Join 205
 QNUA 3 Should use Alias: Column colid should use alias c 123
 QNUA 3 Should use Alias: Column colid should use alias c 124
 QNUA 3 Should use Alias: Column indid should use alias x 124
 QNUA 3 Should use Alias: Column colid should use alias c 126
 QNUA 3 Should use Alias: Column indid should use alias x 126
 QNUA 3 Should use Alias: Column keycnt should use alias x 145
 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]
144
 QTLO 3 Top-Level OR 232
 MRST 2 Result Set Marker 217
 MRST 2 Result Set Marker 239
 MTR1 2 Metrics: Comments Ratio Comments: 38% 28
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 14dec - 2exi + 2 28
 MTR3 2 Metrics: Query Complexity Complexity: 88 28
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 115
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 180

DATA PROPAGATION detailed
ColumnWritten To
@table_namesp_odbc_getindexinfo_rset_001.COLUMN_NAME °.ASC_OR_DESC sp_odbc_getindexinfo_rset_002.COLUMN_NAME °.ASC_OR_DESC
@table_ownersp_odbc_getindexinfo_rset_001.COLUMN_NAME °.ASC_OR_DESC sp_odbc_getindexinfo_rset_002.COLUMN_NAME °.ASC_OR_DESC

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#TmpIndex (1) 
writes table sybsystemprocs..sp_odbc_getindexinfo_rset_001 
reads table sybsystemprocs..sysindexes  
writes table sybsystemprocs..sp_odbc_getindexinfo_rset_002 
reads table sybsystemprocs..syscolumns