DatabaseProcApplicationCreatedLinks
sybsystemprocssp_unbindcache_all  14 déc. 14Defects Propagation Dependencies

1     
2     /* Stored procedure for unbinding all entites bound to a named cache.
3     **
4     **    Databases can only be unbound when using Master.
5     **
6     **	Messages for sp_cacheconfig
7     **
8     ** 17260, "Can't run %1 from within a transaction."
9     **
10    ** 18135, "The specified named cache (%1) does not exist"
11    ** 
12    ** 18352, "Failed to set curwrite label to data_low."
13    **
14    ** 19119, "You cannot use sp_unbindcache_all since 'tempdb' is bound to this cache.
15    **         Instead use sp_unbindcache for each object bound to this cache or run
16    **         sp_unbindcache_all after unbinding 'tempdb' with sp_unbindcache."
17    **
18    ** 19778, "You cannot use sp_unbindcache_all when system or remote local 
19    **	   temporary databases are bound to this cache. Instead use
20    ** 	   sp_unbindcache to unbind each of these databases first."
21    **
22    ** 19974, "Individual object or database cannot be bound to or unbound from
23    **	   cache, '%1!', which is an in-memory storage cache."
24    */
25    create or replace procedure sp_unbindcache_all
26        @cachename varchar(255)
27    as
28    
29        declare @status integer
30        declare @stat int
31        declare @nullarg char(1)
32        declare @dummy int
33        declare @gp_enabled int
34    
35        /*
36        **  Don't allow this in a transaction because we can't undo what
37        **  the bind_cache() builtin has done.
38        */
39        if @@trancount > 0
40        begin
41            /*
42            ** 17260, "Can't run %1! from within a transaction."
43            */
44            raiserror 17260, "sp_unbindcache_all"
45            return (1)
46        end
47        else
48        begin
49            set chained off
50        end
51    
52        set transaction isolation level 1
53    
54        /* 
55        ** If granular permissions is not enabled then sa_role is required.
56        ** If granular permissions is enabled then the permission 'manage data cache' is
57        ** required.  proc_role and proc_auditperm will also do auditing
58        ** if required. Both will also print error message if required.
59        */
60    
61        select @nullarg = NULL
62        execute @status = sp_aux_checkroleperm "sa_role", "manage data cache",
63            @nullarg, @gp_enabled output
64    
65        /* For Auditing */
66        if (@gp_enabled = 0)
67        begin
68            if (proc_role("sa_role") = 0)
69                return (1)
70        end
71        else
72        begin
73            select @dummy = proc_auditperm("manage data cache", @status)
74        end
75    
76        if (@status != 0)
77            return (1)
78    
79        if not exists (select * from master.dbo.sysconfigures where name = @cachename
80                    and config = 19)
81        begin
82            /* 18135, "The specified named cache (%1) does not exist" */
83            raiserror 18135, @cachename
84            return (1)
85        end
86    
87        select @stat = status from master.dbo.sysconfigures
88        where name = @cachename
89            and config = 19
90        if (@stat & 65536 = 65536)
91        begin
92            raiserror 19974, @cachename
93            return (1)
94        end
95    
96    
97    
98        if exists (select 1 from
99                    master.dbo.sysattributes
100               where class = 3 and
101                   attribute = 0 and
102                   object_type = 'D' and
103                   object = 2 and
104                   char_value = @cachename)
105       begin
106           /*
107           ** 19119, "You cannot use sp_unbindcache_all since 'tempdb' is 
108           ** bound to this cache. Instead use sp_unbindcache for each
109           ** object bound to this cache or run sp_unbindcache_all after
110           ** unbinding 'tempdb' with sp_unbindcache."
111           */
112           raiserror 19119
113           return (1)
114       end
115   
116       select @status = config_admin(9, 3, 0, 0, NULL, @cachename)
117   
118       /*
119       **  The builtin returns TRUE for success, FALSE for failure.
120       **  Translate that into the stored procedure lingo where 0 is success.
121       */
122       if (@status = 1)
123       begin
124           return (0)
125       end
126       else
127       begin
128           return (1)
129       end
130   


exec sp_procxmode 'sp_unbindcache_all', 'AnyMode'
go

Grant Execute on sp_unbindcache_all to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns master..sysattributes master..sysattributes
 MINU 4 Unique Index with nullable columns master..sysconfigures master..sysconfigures
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 80
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 89
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 100
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 101
 MGTP 3 Grant to public master..sysattributes  
 MGTP 3 Grant to public master..sysconfigures  
 MGTP 3 Grant to public sybsystemprocs..sp_unbindcache_all  
 MUCO 3 Useless Code Useless Brackets 45
 MUCO 3 Useless Code Useless Brackets 66
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 69
 MUCO 3 Useless Code Useless Brackets 76
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 84
 MUCO 3 Useless Code Useless Brackets 90
 MUCO 3 Useless Code Useless Brackets 93
 MUCO 3 Useless Code Useless Brackets 113
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 128
 QAFM 3 Var Assignment from potentially many rows 87
 QISO 3 Set isolation level 52
 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: {object_type, object, attribute, class}
100
 VNRD 3 Variable is not read @dummy 73
 MSUB 2 Subquery Marker 79
 MSUB 2 Subquery Marker 98
 MTR1 2 Metrics: Comments Ratio Comments: 54% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 15dec - 8exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 53 25

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysattributes (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..syscurconfigs (1)  
   reads table master..sysconfigures (1)  
reads table master..sysconfigures (1)