DatabaseProcApplicationCreatedLinks
sybsystemprocssp_droplockpromote  31 Aug 14Defects 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 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    
36    
37        if upper(@obj_type) not in ("PAGE", "ROW")
38        begin
39            /*
40            ** 18338, "Invalid obj_type parameter: '%1!'. Please specify 'PAGE' or 'ROW'."
41            */
42            raiserror 18338, @obj_type
43            return (1)
44        end
45    
46        if (upper(@scope) = "SERVER")
47        begin
48            /*
49            ** 18169, "Server-wide lock promotion values cannot be dropped.
50            ** Use 'sp_configure' to restore server-wide defaults.
51            */
52            raiserror 18169
53            return (1)
54        end
55    
56        if (upper(@scope) != "DATABASE" and upper(@scope) != "TABLE")
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_drolockpromote"
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        **  Only Accounts with SA role can execute it.
97        **  Call proc_role() with the required SA role.
98        */
99    
100       if (proc_role("sa_role") < 1)
101       begin
102           /* "User must be System Administrator (SA) 
103           ** to configure lock promotion attributes. 
104           */
105           return (1)
106       end
107   
108       if (upper(@scope) = "DATABASE")
109       begin
110           /* If we're dropping lock promotion for a database, 
111           **	make sure we're currently in master. */
112   
113           select @attrib_objid = db_id()
114           if @attrib_objid != 1
115           begin
116               /*
117               ** 18159, "You must be in the 'master' database to add, change 
118               ** or drop lock promotion attribute for a user database."
119               */
120               raiserror 18159
121               return 1
122   
123           end
124   
125           /* Translate dbname to dbid. */
126           select @attrib_objid = 0
127           select @attrib_objid = dbid from master.dbo.sysdatabases
128           where name = @objname
129   
130           if @attrib_objid = 0
131           begin
132               /*
133               ** 17421, "No such database -- run sp_helpdb to list databases."
134               */
135               raiserror 17421
136               return 1
137           end
138   
139   
140           select @object_type = 'D '
141       end
142   
143       if (upper(@scope) = "TABLE")
144       begin
145           /* Translate objname to objid. */
146           select @attrib_objid = 0
147           select @attrib_objid = id, @tab_type = sysstat & 7
148           from sysobjects where id = object_id(@objname)
149   
150           if @attrib_objid = 0
151           begin
152               /* 
153               ** 18090, "The target object does not exist."
154               */
155               raiserror 18090
156               return 1
157   
158           end
159   
160           if @tab_type != 3
161           begin
162               /*
163               ** 18162, "'%1!' is a not a user table. '%2'
164               **  can be used only on user tables."
165               */
166               raiserror 18162, @objname, "sp_dropglockpromote"
167               return 1
168           end
169   
170           select @object_type = 'T '
171       end
172   
173       select @action = 3 /* ATTR_DROP */
174   
175       if upper(@obj_type) = "PAGE"
176           select @attrib = 0 /* page lock promotion attribute */
177       else
178           select @attrib = 1 /* row lock promotion attribute */
179   
180       if not exists (select * from sysattributes where class = 5
181                   and attribute = @attrib
182                   and object_type = @object_type
183                   and object = @attrib_objid)
184       begin
185           /*
186           ** 18166, "Lock promotion attribute does not exist for
187           ** %1,'%2' . Cannot delete it!"
188           */
189           raiserror 18166, @scope, @objname
190           if @@trancount != 0
191               rollback transaction
192           return (1)
193       end
194   
195       begin transaction
196   
197       delete from sysattributes
198       where class = 5
199           and attribute = @attrib
200           and object_type = @object_type
201           and object = @attrib_objid
202   
203       /*
204       ** If there was an error, @@error will be non-zero
205       */
206       if @@error != 0
207       begin
208           if @@trancount != 0
209               rollback transaction
210           return (1)
211       end
212   
213       /* Notify */
214       select @ret = attrib_notify(5, @attrib, @object_type,
215               @attrib_objid, NULL, NULL, NULL, NULL,
216               NULL, NULL, NULL, NULL, NULL, @action)
217   
218       if @ret = 0 /* Unable to notify ? */
219       begin
220           rollback tran
221           return (1)
222       end
223   
224       commit transaction
225   
226       begin
227           /*
228           ** 18167, "Lock promotion attribute of object '%1!' has been dropped!."
229           */
230           exec sp_getmessage 18167, @msg output
231           print @msg, @objname
232       end
233   
234       return (0)
235   


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 180
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 181
 QTYP 4 Comparison type mismatch smallint = int 181
 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
 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 230
 MUCO 3 Useless Code Useless Brackets 43
 MUCO 3 Useless Code Useless Brackets 46
 MUCO 3 Useless Code Useless Brackets 53
 MUCO 3 Useless Code Useless Brackets 56
 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 100
 MUCO 3 Useless Code Useless Brackets 105
 MUCO 3 Useless Code Useless Brackets 108
 MUCO 3 Useless Code Useless Brackets 143
 MUCO 3 Useless Code Useless Brackets 192
 MUCO 3 Useless Code Useless Brackets 210
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Begin-End Pair 226
 MUCO 3 Useless Code Useless Brackets 234
 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}
180
 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
 MSUB 2 Subquery Marker 180
 MTR1 2 Metrics: Comments Ratio Comments: 51% 22
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 11 = 23dec - 14exi + 2 22
 MTR3 2 Metrics: Query Complexity Complexity: 105 22

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

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