DatabaseProcApplicationCreatedLinks
sybsystemprocssp_optbitmap  14 déc. 14Defects Propagation Dependencies

1     
2     
3     /*
4     ** This procedure decodes the bitmap provided as parmater.
5     ** The result is a list of the optimizer options which are included
6     ** in the bitmap.
7     ** A typical argument may be @@optoptions.
8     **
9     ** The procedure creates a fake user opt goal with the bitmap parameter,
10    ** activates this goal and prints the data from sysopions table.
11    */
12    create or replace procedure sp_optbitmap
13        @bitmap varbinary(30)
14    as
15        declare @a int, @name varchar(30)
16    
17    
18        create table #a(a int)
19    
20        select @name = '#' + substring(name, 5, 9)
21        from tempdb..sysobjects
22        where id = object_id('#a')
23    
24        drop table #a
25    
26    
27    
28        delete master..sysattributes where class = 50
29            and object_type = 'qp'
30            and object_cinfo = @name
31    
32    
33        insert master..sysattributes(class, attribute, object_type, object, object_cinfo, char_value)
34        select 50, 0, 'qp', 1, @name, @bitmap
35    
36    
37    
38        execute (" set plan optgoal @name
39    
40    		select 'saved optcriteria'= name from master..sysoptions
41    		where spid = @@spid and currentsetting = '1' and category like 'Query T%' and scope & 32 = 32 ")
42    
43    
44    
45        delete master..sysattributes where class = 50
46            and object_type = 'qp'
47            and object_cinfo = @name
48    
49        return 0
50    


exec sp_procxmode 'sp_optbitmap', 'AnyMode'
go

Grant Execute on sp_optbitmap to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MULT 4 Using literal database 'tempdb' tempdb..sysobjects 21
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 28
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 45
 MAW1 3 Warning message on %name% tempdb..sysobjects.id: Warning message on sysobjects 22
 MDYN 3 Proc uses Dynamic SQL but is not flagged with Dynamic Ownership Chain 12
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sp_optbitmap  
 MGTP 3 Grant to public tempdb..sysobjects  
 MNER 3 No Error Check should check @@error after delete 28
 MNER 3 No Error Check should check @@error after insert 33
 MNER 3 No Error Check should check @@error after delete 45
 MUIN 3 Column created using implicit nullability 18
 MUOT 3 Updates outside transaction 45
 QIWC 3 Insert with not all columns specified missing 9 columns out of 15 33
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_cinfo}
28
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered
(class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo)
Intersection: {class, object_type, object_cinfo}
45
 VUNU 3 Variable is not used @a 15
 MDYS 2 Dynamic SQL Marker 38
 MTR1 2 Metrics: Comments Ratio Comments: 26% 12
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 2 = 1dec - 1exi + 2 12
 MTR3 2 Metrics: Query Complexity Complexity: 22 12

DATA PROPAGATION detailed
ColumnWritten To
@bitmapsysattributes.char_value   sysattributes.int_value   °.char_value   sp_dropdevice_rset_001.device sp_dropdevice_rset_001.device sp_forceonline_object_rset_001.status sp_forceonline_object_rset_002.status sp_forceonline_page_rset_001.status sp_forceonline_page_rset_002.status sp_listsuspect_object_rset_001.Access
sp_listsuspect_page_rset_001.Access sp_makesuspect_obj_rset_001.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_002.Indid °.LogType °.PageType
°.ErrType °.Delay °.TotalNum sp_makesuspect_obj_rset_003.Indid °.LogType °.PageType °.ErrType °.Delay °.TotalNum sp_passwordpolicy_rset_001.message
sp_passwordpolicy_rset_002.value sp_passwordpolicy_rset_003.value sp_passwordpolicy_rset_004.Policy_option sp_rjs_retrieve_rset_001.host_name sp_setsuspect_granularity_rset_001.Online mode sp_ssladmin_rset_001.certificate_path sp_ssladmin_rset_002.Cipher Suite Name °.Preference

DEPENDENCIES
PROCS AND TABLES USED
writes table tempdb..#a (1) 
writes table master..sysattributes (1)  
reads table tempdb..sysobjects (1)