| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_optimal_text_space | | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4 ** 18101, "Table must be in the current database." 5 ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) 6 ** to execute this procedure." 7 ** 18102, "Table '%1!' does not exist in this database." 8 ** 19029, "The status for '%1!' is set to %2!." 9 ** 19031, "Table '%1!' does not have a text or image column." 10 ** 19032, "Due to system failure, the status for '%1!' has not been changed." 11 ** 17756, "The execution of the stored procedure '%1!' in database '%2!' was aborted because there 12 ** was an error in writing the replication log record." 13 ** 18985, "The value for '%1!' attribute must be either 0 or 1." 14 ** 18573, "Failed to update attribute '%1!' for object '%2!'." 15 */ 16 17 create procedure sp_optimal_text_space 18 @objname varchar(767), /* obj we want to mark */ 19 @optname varchar(255), 20 @optvalue int 21 as 22 23 declare @dbname varchar(255) 24 declare @db varchar(255) 25 declare @owner varchar(255) 26 declare @object varchar(255) 27 declare @objid int 28 declare @text_dealloc int 29 declare @dbuid int 30 declare @user_is_sa int 31 declare @user_is_dbo int 32 declare @dbo int 33 declare @dbcc_arg_3 smallint 34 declare @dbcc_arg_4 int 35 36 select @text_dealloc = hextoint("0x020000000") 37 38 select @dbname = db_name() 39 40 if (@optvalue not in (0, 1)) 41 begin 42 /* 43 ** 18985, "The value for attribute '%1!' must be 44 ** either 0 and 1" 45 */ 46 raiserror 18985, @optname 47 return (1) 48 end 49 50 /* 51 ** Crack the name into its corresponding pieces. 52 */ 53 execute sp_namecrack @objname, 54 @db = @db output, 55 @owner = @owner output, 56 @object = @object output 57 58 /* 59 ** Make sure that the object is in the current database. 60 */ 61 if (@db is not NULL and @db != db_name()) 62 begin 63 /* 64 ** 18101, "Table must be in the current database." 65 */ 66 raiserror 18101 67 return (1) 68 end 69 else 70 begin 71 select @dbuid = suid 72 from master..sysdatabases 73 where name = @dbname 74 end 75 76 if (charindex("sa_role", show_role()) > 0) 77 begin 78 select @user_is_sa = 1 79 end 80 else 81 begin 82 select @user_is_sa = 0 83 end 84 85 exec sybsystemprocs.dbo.sp_is_valid_user @dbo output, @dbname 86 if (@dbo = 1) 87 begin 88 select @user_is_dbo = 1 89 end 90 else 91 begin 92 select @user_is_dbo = 0 93 end 94 95 /* 96 ** Check is the user has the correct roles to change 97 ** this table property. 98 */ 99 if (@user_is_dbo = 0 and @user_is_sa = 0) 100 begin 101 /* 102 ** 17230, "You must be the System Administrator (SA) or the Database Owner (dbo) 103 ** to execute this procedure." 104 */ 105 raiserror 17230 106 return (1) 107 end 108 109 /* 110 ** Make sure that the object actually exists. 111 */ 112 select @objid = object_id(@objname) 113 114 if (@objid is NULL) or 115 (not exists (select name from sysobjects 116 where id = @objid and 117 type = "U")) 118 begin 119 /* 120 ** 18102, "Table '%1!' does not exist in this database." 121 */ 122 raiserror 18102, @objname 123 return (1) 124 end 125 126 /* 127 ** Check if the object has text/image columns in the first 128 ** place. 129 */ 130 if not exists (select 1 131 from sysindexes 132 where (indid = 255) and 133 (id = @objid)) 134 begin 135 /* 136 ** 19031, "Table '%1!' does not have a text or image column." 137 */ 138 raiserror 19031, @objname 139 return (1) 140 end 141 142 /* 143 ** This transaction also writes a log record for replicating the 144 ** invocation of this procedure. If logexec() fails, the transaction 145 ** is aborted. 146 ** 147 ** IMPORTANT: The name rs_logexec is significant and is used by 148 ** Replication Server. 149 */ 150 begin transaction rs_logexec 151 152 if (@optvalue = 1) 153 begin 154 select @dbcc_arg_3 = 1 155 156 update sysobjects 157 set sysstat2 = sysstat2 | @text_dealloc 158 from sysobjects 159 where id = @objid 160 end 161 else 162 begin 163 select @dbcc_arg_3 = 0 164 165 update sysobjects 166 set sysstat2 = sysstat2 & ~ @text_dealloc 167 from sysobjects 168 where id = @objid 169 end 170 171 if (@@error != 0) 172 begin 173 /* 174 ** Error is already raised so just rollback. 175 */ 176 rollback transaction 177 178 return (1) 179 end 180 181 select @dbcc_arg_4 = 0 182 dbcc chgindcachedvalue(@optname, @objname, @dbcc_arg_3, @dbcc_arg_4) 183 184 /* If there was an error, @@error will be non-zero */ 185 if (@@error != 0) 186 begin 187 rollback transaction 188 189 /* 190 ** 18573, "Failed to update attribute '%1!' for object '%2!'." 191 */ 192 raiserror 18573, @optname, @objname 193 return (1) 194 end 195 196 197 /* 198 ** Write the log record to replicate this invocation 199 ** of the stored procedure. 200 */ 201 if (logexec() != 1) 202 begin 203 rollback transaction 204 205 /* 206 ** 17756, "The execution of the stored procedure '%1!' 207 ** in database '%2!' was aborted because there 208 ** was an error in writing the replication log 209 ** record." 210 */ 211 raiserror 17756, "sp_optimal_text_space", @dbname 212 return (1) 213 end 214 215 commit transaction 216 217 return (0) 218 219
exec sp_procxmode 'sp_optimal_text_space', 'AnyMode' go Grant Execute on sp_optimal_text_space to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table sybsystemprocs..sysindexes reads table master..sysdatabases (1)  calls proc sybsystemprocs..sp_namecrack read_writes table sybsystemprocs..sysobjects calls proc sybsystemprocs..sp_is_valid_user  reads table master..sysdatabases (1) read_writes table tempdb..#t (1) reads table master..sysloginroles (1) CALLERS called by proc sybsystemprocs..sp_chgattribute |
