DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_getindexinfo  14 déc. 14Defects Propagation Dependencies

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


exec sp_procxmode 'sp_jdbc_getindexinfo', 'AnyMode'
go

Grant Execute on sp_jdbc_getindexinfo to public
go
RESULT SETS
sp_jdbc_getindexinfo_rset_002
sp_jdbc_getindexinfo_rset_001

DEFECTS
 MTYP 4 Assignment type mismatch INDEX_QUALIFIER: varchar(32) = longsysname(255) 143
 QCSC 4 Costly 'select count()', use 'exists()' 72
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
225
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 133
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 134
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 155
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 168
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 169
 QTYP 4 Comparison type mismatch smallint = int 169
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 189
 QTYP 4 Comparison type mismatch smallint = int 189
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 190
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 213
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 225
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 103
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 122
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 132
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 152
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 153
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 165
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 166
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 166
 MAW1 3 Warning message on %name% sybsystemprocs..syscolumns.id: Warning message on syscolumns 167
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 167
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 188
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 210
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 211
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 223
 MAW1 3 Warning message on %name% sybsystemprocs..sysindexes.id: Warning message on sysindexes 224
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 224
 MGTP 3 Grant to public sybsystemprocs..sp_jdbc_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 48
 MNER 3 No Error Check should check return value of exec 67
 MNER 3 No Error Check should check return value of exec 77
 MNER 3 No Error Check should check @@error after insert 138
 MNER 3 No Error Check should check @@error after update 175
 MNER 3 No Error Check should check @@error after insert 196
 MNER 3 No Error Check should check @@error after update 240
 MUCO 3 Useless Code Useless Brackets in create proc 7
 MUCO 3 Useless Code Useless Brackets 21
 MUCO 3 Useless Code Useless Brackets 27
 MUCO 3 Useless Code Useless Brackets 50
 MUCO 3 Useless Code Useless Brackets 57
 MUCO 3 Useless Code Useless Brackets 60
 MUCO 3 Useless Code Useless Brackets 65
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 292
 MUCO 3 Useless Code Useless Brackets 295
 MUIN 3 Column created using implicit nullability 82
 QCRS 3 Conditional Result Set 250
 QCRS 3 Conditional Result Set 270
 QISO 3 Set isolation level 55
 QNAJ 3 Not using ANSI Inner Join 164
 QNAJ 3 Not using ANSI Inner Join 222
 QNUA 3 Should use Alias: Column colid should use alias c 147
 QNUA 3 Should use Alias: Column colid should use alias c 148
 QNUA 3 Should use Alias: Column indid should use alias x 148
 QNUA 3 Should use Alias: Column keycnt should use alias x 168
 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]
167
 VNRD 3 Variable is not read @table_qualifier 53
 CRDO 2 Read Only Cursor Marker (has for read only clause) 103
 MRST 2 Result Set Marker 250
 MRST 2 Result Set Marker 270
 MSUB 2 Subquery Marker 72
 MTR1 2 Metrics: Comments Ratio Comments: 26% 7
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 19dec - 4exi + 2 7
 MTR3 2 Metrics: Query Complexity Complexity: 112 7
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 139
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 197

DEPENDENCIES
PROCS AND TABLES USED
read_writes table tempdb..#TmpIndex (1) 
writes table sybsystemprocs..sp_jdbc_getindexinfo_rset_001 
reads table sybsystemprocs..sysobjects  
writes table sybsystemprocs..sp_jdbc_getindexinfo_rset_002 
reads table sybsystemprocs..sysindexes  
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)  
reads table sybsystemprocs..syscolumns