Database | Proc | Application | Created | Links |
sybsystemprocs | sp_renamedb ![]() | ![]() | 31 Aug 14 | Defects Dependencies |
1 2 /* Sccsid = "%Z% generic/sproc/src/%M% %I% %G%" */ 3 /* 4.8 1.1 06/14/90 sproc/src/renamedb */ 4 5 /* 6 ** Messages for "sp_renamedb" 17790 7 ** 8 ** 17260, "Can't run %1! from within a transaction." 9 ** 17590, "The specified database does not exist." 10 ** 17240, "'%1!' is not a valid name." 11 ** 17791, "A database with the new name already exists." 12 ** 17792, "The databases master, model, tempdb, sybsecurity, sybsystemprocs and mounted_sybsystemprocs cannot be renamed." 13 ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'." 14 ** 17794, "Database is renamed and in single-user mode." 15 ** 17795, "System Administrator (SA) must reset it to multi-user mode with sp_dboption." 16 ** 17902, "You cannot run stored procedure '%1!' from a low durability database." 17 ** 18850, "HA Error: Database '%1!' is a system proxy database. You must execute sp_renamedb on the primary server first." 18 ** 18851, "HA Error: You must be in the master database in order to run '%1!' against a system proxy database." 19 ** 18843, "Please check the System Administration Guide to determine how to %1! the corresponding proxy or real database on the companion server '%2!'." 20 */ 21 22 23 24 /* 25 ** IMPORTANT NOTE: 26 ** This stored procedure uses the built-in function db_id() in the 27 ** where clause of a select query. If you intend to change this query 28 ** or use the object_id() or db_id() builtin in this procedure, please read the 29 ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules 30 ** pertaining to object-id's and db-id's outlined there, are followed. 31 */ 32 33 create procedure sp_renamedb 34 @dbname sysname(30), /* old (current) db name */ 35 @newname sysname(30) /* new name we want to call it */ 36 as 37 38 declare @msg varchar(1024) 39 declare @bitdesc varchar(30) /* bit description for the db */ 40 declare @HA_CERTIFIED tinyint /* Is the SP HA certified ? */ 41 declare @retstat int 42 declare @maxobjlen int 43 declare @rollback int 44 45 select @HA_CERTIFIED = 0 46 47 48 49 /* check to see if we are using HA specific SP for a HA enabled server */ 50 exec @retstat = sp_ha_check_certified 'sp_renamedb', @HA_CERTIFIED 51 if (@retstat != 0) 52 return (1) 53 54 /* 55 ** If we're in a transaction, disallow this since it might make recovery 56 ** impossible. 57 */ 58 if @@trancount > 0 59 begin 60 /* 61 ** 17260, "Can't run %1! from within a transaction." 62 */ 63 raiserror 17260, "sp_renamedb" 64 return (1) 65 end 66 else 67 begin 68 set chained off 69 end 70 71 set transaction isolation level 1 72 73 /* check if user has sa role, proc_role will also do auditing 74 ** if required. proc_role will also print error message if required. 75 */ 76 77 if (proc_role("sa_role") = 0) 78 return (1) 79 80 /* 81 ** Make sure the database exists. 82 */ 83 if not exists (select * 84 from master.dbo.sysdatabases 85 where name = @dbname) 86 begin 87 /* 88 ** 17590, "The specified database does not exist." 89 */ 90 raiserror 17590 91 return (1) 92 end 93 94 /* 95 ** Make sure that the @newname db doesn't already exist. 96 */ 97 if exists (select * 98 from master.dbo.sysdatabases 99 where name = @newname) 100 begin 101 /* 102 ** 17791, "A database with the new name already exists." 103 */ 104 raiserror 17791 105 return (1) 106 end 107 108 /* 109 ** Check to see that the @newname is valid. 110 */ 111 select @maxobjlen = length from master.dbo.syscolumns 112 where id = object_id("master.dbo.sysdatabases") 113 and name = 'name' 114 115 if valid_name(@newname, @maxobjlen) = 0 116 begin 117 /* 118 ** 17240, "'%1!' is not a valid name." 119 */ 120 raiserror 17240, @newname 121 return (1) 122 end 123 124 /* 125 ** Don't allow the names of master, tempdb, and model to be changed. 126 */ 127 if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "mounted_sybsystemprocs") 128 begin 129 /* 130 ** 17792, "The databases master, model, tempdb, sybsecurity, 131 ** sybsystemprocs and mounted_sybsystemprocs cannot be renamed." 132 */ 133 raiserror 17792 134 return (1) 135 end 136 137 138 /* 139 ** Check single user bit (4096) 140 ** Database must be in single user mode to necessitate the rid update in the 141 ** database's DBTABLE 142 */ 143 select @bitdesc = null 144 select @bitdesc = v.name 145 from master.dbo.spt_values v, master.dbo.sysdatabases d 146 where d.dbid = db_id(@dbname) 147 and v.type = "D" 148 and d.status & v.number = 4096 149 if @bitdesc is null 150 begin 151 /* 152 ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'." 153 */ 154 raiserror 17793, @dbname, "sp_renamedb" 155 return (1) 156 end 157 158 159 160 /* 161 ** This stored procedure can not be executed from a low durablity database 162 ** as the changes made by follwoing 'dbcc chgdbname' can not be undone 163 ** as the execution will not come back to stored procedure when error 164 ** 3952 happens. So we check if we are in a low durability database before 165 ** starting execution of the stored procedure. 166 */ 167 if db_attr(db_name(), "durability") != 'full' 168 begin 169 raiserror 17902, "sp_renamedb" 170 return (1) 171 end 172 173 /* 174 ** Update the dbinfo in the sysindexes row for syslogs of the database 175 ** whose name is being changed. Also the dbtable structure for the db 176 ** in question is updated with the new name. 177 ** 178 ** NOTE: the following dbcc command relies on the above commands executing. 179 ** Using this command outside of this procedure can cause a host of 180 ** perfidious problems. 181 */ 182 dbcc chgdbname(@dbname, @newname) 183 184 if @@error = 0 185 begin 186 select @rollback = 1 187 begin tran rename_db 188 update master.dbo.syslogins 189 set dbname = @newname 190 where dbname = @dbname 191 if @@error = 0 192 begin 193 update master.dbo.sysdatabases 194 set name = @newname 195 where name = @dbname 196 if @@error = 0 197 begin 198 commit tran rename_db 199 set @rollback = @@error 200 end 201 end 202 203 if @rollback != 0 204 begin 205 rollback tran rename_db 206 dbcc chgdbname(@newname, @dbname) 207 return (1) 208 end 209 end 210 else 211 return (1) 212 213 /* 214 ** 17794, "Database is renamed and in single-user mode." 215 */ 216 exec sp_getmessage 17794, @msg output 217 print @msg 218 219 /* 220 ** 17795, "System Administrator (SA) must reset it to multi-user mode with sp_dboption." 221 */ 222 exec sp_getmessage 17795, @msg output 223 print @msg 224 225 226 227 return (0) 228
exec sp_procxmode 'sp_renamedb', 'AnyMode' go Grant Execute on sp_renamedb to public go
DEPENDENCIES |
PROCS AND TABLES USED read_writes table master..sysdatabases (1) ![]() reads table master..spt_values (1) ![]() writes table master..syslogins (1) ![]() calls proc sybsystemprocs..sp_ha_check_certified ![]() reads table tempdb..sysobjects (1) ![]() reads table master..syscolumns (1) ![]() calls proc sybsystemprocs..sp_getmessage ![]() calls proc sybsystemprocs..sp_validlang ![]() reads table master..syslanguages (1) ![]() reads table master..syslanguages (1) ![]() reads table sybsystemprocs..sysusermessages ![]() reads table master..sysmessages (1) ![]() |