DatabaseProcApplicationCreatedLinks
sybsystemprocssp_ijdbc_getindexinfo  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_ijdbc_getindexinfo', 'AnyMode'
go

Grant Execute on sp_ijdbc_getindexinfo to public
go
DEFECTS
 QCSC 4 Costly 'select count()', use 'exists()' 73
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
204
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 125
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 126
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 146
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 160
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 193
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 204
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 106
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 121
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 124
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 124
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 143
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 144
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 153
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 156
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 156
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 157
 MAW1 3 Warning message on %name% tempdb..#tmp_indid.id: Warning message on #tmp_indid_crby_sybsystemprocs__sp_ijdbc_getindexinfo 157
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 158
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 158
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 159
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 159
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 171
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 190
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 191
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 200
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 202
 MAW1 3 Warning message on %name% tempdb..#tmp_id.id: Warning message on #tmp_id_crby_sybsystemprocs__sp_ijdbc_getindexinfo 202
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 203
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 203
 MGTP 3 Grant to public sybsystemprocs..sp_ijdbc_getindexinfo  
 MGTP 3 Grant to public sybsystemprocs..syscolumns  
 MGTP 3 Grant to public sybsystemprocs..sysindexes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 37
 MNER 3 No Error Check should check return value of exec 49
 MNER 3 No Error Check should check return value of exec 68
 MNER 3 No Error Check should check return value of exec 78
 MNER 3 No Error Check should check @@error after delete 83
 MNER 3 No Error Check should check @@error after select into 106
 MNER 3 No Error Check should check @@error after select into 121
 MNER 3 No Error Check should check @@error after insert 129
 MNER 3 No Error Check should check @@error after update 167
 MNER 3 No Error Check should check @@error after insert 177
 MNER 3 No Error Check should check @@error after update 211
 MNER 3 No Error Check should check @@error after insert 221
 MNER 3 No Error Check should check @@error after insert 242
 MUCO 3 Useless Code Useless Brackets in create proc 15
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 51
 MUCO 3 Useless Code Useless Brackets 61
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 70
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 218
 MUCO 3 Useless Code Useless Brackets 265
 MUIN 3 Column created using implicit nullability 85
 QDIS 3 Check correct use of 'select distinct' 121
 QGWO 3 Group by/Distinct/Union without order by 121
 QISO 3 Set isolation level 58
 QJWT 3 Join or Sarg Without Index on temp table 157
 QJWT 3 Join or Sarg Without Index on temp table 171
 QNAJ 3 Not using ANSI Inner Join 123
 QNAJ 3 Not using ANSI Inner Join 154
 QNAJ 3 Not using ANSI Inner Join 169
 QNAJ 3 Not using ANSI Inner Join 201
 QNUA 3 Should use Alias: Column colid should use alias c 138
 QNUA 3 Should use Alias: Column colid should use alias c 139
 QNUA 3 Should use Alias: Column colid should use alias c 140
 QNUA 3 Should use Alias: Column keycnt should use alias x 160
 QNUA 3 Should use Alias: Table #tmp_id 169
 QNUA 3 Should use Alias: Column status should use alias #TmpIndex 170
 QNUA 3 Should use Alias: Column id should use alias #tmp_id 171
 QNUA 3 Should use Alias: Column table_id should use alias #TmpIndex 171
 QTJ1 3 Table only appears in inner join clause 169
 VNRD 3 Variable is not read @table_qualifier 42
 VUNU 3 Variable is not used @indid 22
 VUNU 3 Variable is not used @lastindid 23
 VUNU 3 Variable is not used @tableid 25
 MSUB 2 Subquery Marker 73
 MTR1 2 Metrics: Comments Ratio Comments: 25% 15
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 10 = 13dec - 5exi + 2 15
 MTR3 2 Metrics: Query Complexity Complexity: 97 15

DEPENDENCIES
PROCS AND TABLES USED
reads table sybsystemprocs..sysindexes  
read_writes table tempdb..#tmp_indid (1) 
reads table sybsystemprocs..sysobjects  
read_writes table tempdb..#tmp_id (1) 
calls proc sybsystemprocs..sp_getmessage  
   reads table sybsystemprocs..sysusermessages  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
read_writes table tempdb..#TmpIndex (1) 
writes table tempdb..#tmp_getindexinfo (1) 
reads table sybsystemprocs..syscolumns