DatabaseProcApplicationCreatedLinks
sybsystemprocssp_unbindcache_all  31 Aug 14Defects 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 procedure sp_unbindcache_all
26        @cachename varchar(255)
27    as
28    
29        declare @status integer
30        declare @stat int
31    
32        /*
33        **  Don't allow this in a transaction because we can't undo what
34        **  the bind_cache() builtin has done.
35        */
36        if @@trancount > 0
37        begin
38            /*
39            ** 17260, "Can't run %1! from within a transaction."
40            */
41            raiserror 17260, "sp_unbindcache_all"
42            return (1)
43        end
44        else
45        begin
46            set chained off
47        end
48    
49        set transaction isolation level 1
50    
51        /* check if user has sa role, proc_role will also do auditing
52        ** if required. proc_role will also print error message if required.
53        */
54        if (proc_role("sa_role") = 0)
55            return (1)
56    
57    
58    
59        if not exists (select * from master.dbo.sysconfigures where name = @cachename
60                    and config = 19)
61        begin
62            /* 18135, "The specified named cache (%1) does not exist" */
63            raiserror 18135, @cachename
64            return (1)
65        end
66    
67    
68    
69        select @stat = status from master.dbo.sysconfigures
70        where name = @cachename
71            and config = 19
72        if (@stat & 65536 = 65536)
73        begin
74            raiserror 19974, @cachename
75            return (1)
76        end
77    
78    
79    
80        if exists (select 1 from
81                    master.dbo.sysattributes
82                where class = 3 and
83                    attribute = 0 and
84                    object_type = 'D' and
85                    object = 2 and
86                    char_value = @cachename)
87        begin
88            /*
89            ** 19119, "You cannot use sp_unbindcache_all since 'tempdb' is 
90            ** bound to this cache. Instead use sp_unbindcache for each
91            ** object bound to this cache or run sp_unbindcache_all after
92            ** unbinding 'tempdb' with sp_unbindcache."
93            */
94            raiserror 19119
95            return (1)
96        end
97    
98        select @status = config_admin(9, 3, 0, 0, NULL, @cachename)
99    
100       /*
101       **  The builtin returns TRUE for success, FALSE for failure.
102       **  Translate that into the stored procedure lingo where 0 is success.
103       */
104       if (@status = 1)
105       begin
106           return (0)
107       end
108       else
109       begin
110           return (1)
111       end
112   


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 60
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 71
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 82
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 83
 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 42
 MUCO 3 Useless Code Useless Brackets 54
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 64
 MUCO 3 Useless Code Useless Brackets 72
 MUCO 3 Useless Code Useless Brackets 75
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 104
 MUCO 3 Useless Code Useless Brackets 106
 MUCO 3 Useless Code Useless Brackets 110
 QAFM 3 Var Assignment from potentially many rows 69
 QISO 3 Set isolation level 49
 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}		 82
 MSUB 2 Subquery Marker 59
 MSUB 2 Subquery Marker 80
 MTR1 2 Metrics: Comments Ratio Comments: 58% 25
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 12dec - 7exi + 2 25
 MTR3 2 Metrics: Query Complexity Complexity: 42 25

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysconfigures (1)  
reads table master..sysattributes (1)