Database | Proc | Application | Created | Links |
sybsystemprocs | sp_droplockpromote | 31 Aug 14 | Defects 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 |