DatabaseProcApplicationCreatedLinks
sybsystemprocssp_droplockpromote  14 déc. 14Defects Propagation Dependencies

1     
2     /* Stored procedure for dropping lock promotion attributes. 
3     ** Database attributes can only be modified when using Master.
4     */
5     
6     /*
7     ** Messages for "sp_droplockpromote"
8     **
9     ** 17260, "Can't run %1! from within a transaction."
10    ** 17421, "No such database -- run sp_helpdb to list databases."
11    ** 17460, "Object must be in the current database."
12    ** 18090, "The target object does not exist."
13    ** 18159, "You must be in the 'master' database to add, change or drop lock promotion attribute for a user database."
14    ** 18162, "'%1!' is a not a user table. '%2' can be used only on user tables."
15    ** 18166, "Lock promotion attribute does not exist for %1, '%2'. Cannot delete it."
16    ** 18167, "Lock promotion attribute of object '%1!' has been dropped!."
17    ** 18168, "Invalid value '%1', specified for 'scope' parameter. Valid values are 'DATABASE' or 'TABLE'."
18    ** 18169, "Server-wide lock promotion values cannot be dropped. Use 'sp_configure' to restore server-wide defaults.
19    ** 18338, "Invalid obj_type parameter: '%1!'. Please specify 'PAGE' or 'ROW'."
20    */
21    
22    create or replace procedure sp_droplockpromote
23        @obj_type varchar(10), /* page or row */
24        @scope varchar(10), /* table, database or server */
25        @objname varchar(767) = NULL /* table or database name */
26    as
27    
28        declare @attrib_objid int /* object id of the table/db */
29        declare @object_type varchar(2) /* object type of the table/db */
30        declare @attrib int /* attrib type of the optname */
31        declare @msg varchar(1024) /* message buffer */
32        declare @action int /* DROP sysattributes row */
33        declare @ret int /* Return value of built-in: attrib_notify. */
34        declare @tab_type smallint /* User table ? */
35        declare @nullarg char(1)
36        declare @dummy int
37        declare @status int
38        declare @gp_enabled int
39    
40    
41        if upper(@obj_type) not in ("PAGE", "ROW")
42        begin
43            /*
44            ** 18338, "Invalid obj_type parameter: '%1!'. Please specify 'PAGE' or 'ROW'."
45            */
46            raiserror 18338, @obj_type
47            return (1)
48        end
49    
50        if (upper(@scope) = "SERVER")
51        begin
52            /*
53            ** 18169, "Server-wide lock promotion values cannot be dropped.
54            ** Use 'sp_configure' to restore server-wide defaults.
55            */
56            raiserror 18169
57            return (1)
58        end
59    
60        if (upper(@scope) != "DATABASE" and upper(@scope) != "TABLE")
61        begin
62            /*
63            ** 18168, "Invalid value '%1', specified for 'scope' parameter
64            ** Valid values are 'DATABASE' or 'TABLE'."
65            */
66            raiserror 18168, @scope
67            return (1)
68        end
69    
70        /* If we're in a transaction, disallow this */
71        if @@trancount > 0
72        begin
73            /*
74            ** 17260, "Can't run %1! from within a transaction."
75            */
76            raiserror 17260, "sp_drolockpromote"
77            return (1)
78        end
79        else
80        begin
81            set chained off
82        end
83    
84        set transaction isolation level 1
85    
86        /*
87        **  Make sure the @objname is local to the current database.
88        */
89        if @objname like "%.%.%" and
90            substring(@objname, 1, charindex(".", @objname) - 1) != db_name()
91        begin
92            /*
93            ** 17460, "Object must be in the current database."
94            */
95            raiserror 17460
96            return (1)
97        end
98    
99        /* 
100       ** If granular permissions is not enabled then sa_role is required.
101       ** If granular permissions is enabled then the permission 'manage lock 
102       ** promotion threshold' is required.  proc_role and proc_auditperm will also 
103       ** do auditing if required. Both will also print error message if required.
104       */
105   
106       select @nullarg = NULL
107       execute @status = sp_aux_checkroleperm "sa_role",
108           "manage lock promotion threshold",
109           @nullarg, @gp_enabled output
110   
111       /* For Auditing */
112       if (@gp_enabled = 0)
113       begin
114           if (proc_role("sa_role") = 0)
115               return (1)
116       end
117       else
118       begin
119           select @dummy = proc_auditperm("manage lock promotion threshold",
120                   @status)
121       end
122   
123       if (@status != 0)
124           return (1)
125   
126       if (upper(@scope) = "DATABASE")
127       begin
128           /* If we're dropping lock promotion for a database, 
129           **	make sure we're currently in master. */
130   
131           select @attrib_objid = db_id()
132           if @attrib_objid != 1
133           begin
134               /*
135               ** 18159, "You must be in the 'master' database to add, change 
136               ** or drop lock promotion attribute for a user database."
137               */
138               raiserror 18159
139               return 1
140   
141           end
142   
143           /* Translate dbname to dbid. */
144           select @attrib_objid = 0
145           select @attrib_objid = dbid from master.dbo.sysdatabases
146           where name = @objname
147   
148           if @attrib_objid = 0
149           begin
150               /*
151               ** 17421, "No such database -- run sp_helpdb to list databases."
152               */
153               raiserror 17421
154               return 1
155           end
156   
157   
158           select @object_type = 'D '
159       end
160   
161       if (upper(@scope) = "TABLE")
162       begin
163           /* Translate objname to objid. */
164           select @attrib_objid = 0
165           select @attrib_objid = id, @tab_type = sysstat & 7
166           from sysobjects where id = object_id(@objname)
167   
168           if @attrib_objid = 0
169           begin
170               /* 
171               ** 18090, "The target object does not exist."
172               */
173               raiserror 18090
174               return 1
175   
176           end
177   
178           if @tab_type != 3
179           begin
180               /*
181               ** 18162, "'%1!' is a not a user table. '%2'
182               **  can be used only on user tables."
183               */
184               raiserror 18162, @objname, "sp_dropglockpromote"
185               return 1
186           end
187   
188           select @object_type = 'T '
189       end
190   
191       select @action = 3 /* ATTR_DROP */
192   
193       if upper(@obj_type) = "PAGE"
194           select @attrib = 0 /* page lock promotion attribute */
195       else
196           select @attrib = 1 /* row lock promotion attribute */
197   
198       if not exists (select * from sysattributes where class = 5
199                   and attribute = @attrib
200                   and object_type = @object_type
201                   and object = @attrib_objid)
202       begin
203           /*
204           ** 18166, "Lock promotion attribute does not exist for
205           ** %1,'%2' . Cannot delete it!"
206           */
207           raiserror 18166, @scope, @objname
208           if @@trancount != 0
209               rollback transaction
210           return (1)
211       end
212   
213       begin transaction
214   
215       delete from sysattributes
216       where class = 5
217           and attribute = @attrib
218           and object_type = @object_type
219           and object = @attrib_objid
220   
221       /*
222       ** If there was an error, @@error will be non-zero
223       */
224       if @@error != 0
225       begin
226           if @@trancount != 0
227               rollback transaction
228           return (1)
229       end
230   
231       /* Notify */
232       select @ret = attrib_notify(5, @attrib, @object_type,
233               @attrib_objid, NULL, NULL, NULL, NULL,
234               NULL, NULL, NULL, NULL, NULL, @action)
235   
236       if @ret = 0 /* Unable to notify ? */
237       begin
238           rollback tran
239           return (1)
240       end
241   
242       commit transaction
243   
244       begin
245           /*
246           ** 18167, "Lock promotion attribute of object '%1!' has been dropped!."
247           */
248           exec sp_getmessage 18167, @msg output
249           print @msg, @objname
250       end
251   
252       return (0)
253   


exec sp_procxmode 'sp_droplockpromote', 'AnyMode'
go

Grant Execute on sp_droplockpromote to public
go
DEFECTS
 MINU 4 Unique Index with nullable columns sybsystemprocs..sysattributes sybsystemprocs..sysattributes
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 198
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 199
 QTYP 4 Comparison type mismatch smallint = int 199
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 216
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 217
 QTYP 4 Comparison type mismatch smallint = int 217
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 165
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 166
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_droplockpromote  
 MGTP 3 Grant to public sybsystemprocs..sysattributes  
 MGTP 3 Grant to public sybsystemprocs..sysobjects  
 MNER 3 No Error Check should check return value of exec 248
 MUCO 3 Useless Code Useless Brackets 47
 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 67
 MUCO 3 Useless Code Useless Brackets 77
 MUCO 3 Useless Code Useless Brackets 96
 MUCO 3 Useless Code Useless Brackets 112
 MUCO 3 Useless Code Useless Brackets 114
 MUCO 3 Useless Code Useless Brackets 115
 MUCO 3 Useless Code Useless Brackets 123
 MUCO 3 Useless Code Useless Brackets 124
 MUCO 3 Useless Code Useless Brackets 126
 MUCO 3 Useless Code Useless Brackets 161
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 228
 MUCO 3 Useless Code Useless Brackets 239
 MUCO 3 Useless Code Useless Begin-End Pair 244
 MUCO 3 Useless Code Useless Brackets 252
 QISO 3 Set isolation level 84
 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}
198
 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}
216
 VNRD 3 Variable is not read @dummy 119
 MSUB 2 Subquery Marker 198
 MTR1 2 Metrics: Comments Ratio Comments: 49% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 14 = 26dec - 14exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 117 22

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
reads table master..sysdatabases (1)  
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
   reads table sybsystemprocs..sysusermessages  
read_writes table sybsystemprocs..sysattributes  
reads table sybsystemprocs..sysobjects  

CALLERS
called by proc sybsystemprocs..sp_dropglockpromote  
called by proc sybsystemprocs..sp_droprowlockpromote