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


exec sp_procxmode 'sp_droplockpromote_ptn', 'AnyMode'
go

Grant Execute on sp_droplockpromote_ptn 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 217
 QTYP 4 Comparison type mismatch smallint = int 217
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 218
 QTYP 4 Comparison type mismatch smallint = int 218
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 235
 QTYP 4 Comparison type mismatch smallint = int 235
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 236
 QTYP 4 Comparison type mismatch smallint = int 236
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 187
 MAW1 3 Warning message on %name% sybsystemprocs..sysobjects.id: Warning message on sysobjects 188
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public sybsystemprocs..sp_droplockpromote_ptn  
 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 276
 MUCO 3 Useless Code Useless Brackets 52
 MUCO 3 Useless Code Useless Brackets 55
 MUCO 3 Useless Code Useless Brackets 63
 MUCO 3 Useless Code Useless Brackets 73
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 111
 MUCO 3 Useless Code Useless Brackets 119
 MUCO 3 Useless Code Useless Brackets 120
 MUCO 3 Useless Code Useless Brackets 122
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 183
 MUCO 3 Useless Code Useless Brackets 229
 MUCO 3 Useless Code Useless Brackets 247
 MUCO 3 Useless Code Useless Brackets 251
 MUCO 3 Useless Code Useless Brackets 267
 MUCO 3 Useless Code Useless Begin-End Pair 272
 MUCO 3 Useless Code Useless Brackets 280
 QISO 3 Set isolation level 80
 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}
217
 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}
235
 VNRD 3 Variable is not read @dummy 115
 MSUB 2 Subquery Marker 217
 MTR1 2 Metrics: Comments Ratio Comments: 46% 21
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 17 = 29dec - 14exi + 2 21
 MTR3 2 Metrics: Query Complexity Complexity: 130 21

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table sybsystemprocs..sysattributes  
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  
reads table master..sysdatabases (1)  
reads table sybsystemprocs..sysobjects  

CALLERS
called by proc sybsystemprocs..sp_dropglockpromote_ptn  
called by proc sybsystemprocs..sp_droprowlockpromote_ptn