DatabaseProcApplicationCreatedLinks
sybsystemprocssp_jdbc_getindexinfo  31 Aug 14Defects Dependencies

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


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) 145
 QCSC 4 Costly 'select count()', use 'exists()' 74
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: sysindexes.csysindexes unique clustered
(id, indid)
Intersection: {indid}
227
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 135
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 136
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 157
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 170
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 171
 QTYP 4 Comparison type mismatch smallint = int 171
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 191
 QTYP 4 Comparison type mismatch smallint = int 191
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 192
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 215
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 227
 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 50
 MNER 3 No Error Check should check return value of exec 69
 MNER 3 No Error Check should check return value of exec 79
 MNER 3 No Error Check should check @@error after insert 140
 MNER 3 No Error Check should check @@error after update 177
 MNER 3 No Error Check should check @@error after insert 198
 MNER 3 No Error Check should check @@error after update 242
 MUCO 3 Useless Code Useless Brackets in create proc 9
 MUCO 3 Useless Code Useless Brackets 23
 MUCO 3 Useless Code Useless Brackets 29
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 62
 MUCO 3 Useless Code Useless Brackets 67
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 74
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 249
 MUCO 3 Useless Code Useless Brackets 294
 MUCO 3 Useless Code Useless Brackets 297
 MUIN 3 Column created using implicit nullability 84
 QCRS 3 Conditional Result Set 252
 QCRS 3 Conditional Result Set 272
 QISO 3 Set isolation level 57
 QNAJ 3 Not using ANSI Inner Join 166
 QNAJ 3 Not using ANSI Inner Join 224
 QNUA 3 Should use Alias: Column colid should use alias c 149
 QNUA 3 Should use Alias: Column colid should use alias c 150
 QNUA 3 Should use Alias: Column indid should use alias x 150
 QNUA 3 Should use Alias: Column keycnt should use alias x 170
 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]
169
 VNRD 3 Variable is not read @table_qualifier 55
 VUNU 3 Variable is not used @approximate 14
 CRDO 2 Read Only Cursor Marker (has for read only clause) 105
 MRST 2 Result Set Marker 252
 MRST 2 Result Set Marker 272
 MSUB 2 Subquery Marker 74
 MTR1 2 Metrics: Comments Ratio Comments: 26% 9
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 16 = 18dec - 4exi + 2 9
 MTR3 2 Metrics: Query Complexity Complexity: 112 9
 PRED_QUERY_COLLECTION 2 {c=sybsystemprocs..syscolumns, i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 141
 PRED_QUERY_COLLECTION 2 {i=sybsystemprocs..sysindexes, o=sybsystemprocs..sysobjects} 0 199

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