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

DEFECTS
QJWI 5 Join or Sarg Without Index	148
MCTR 4 Conditional Begin Tran or Commit Tran	187
MCTR 4 Conditional Begin Tran or Commit Tran	198
QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered (number, type) Intersection: {type}	147
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int	146
QTYP 4 Comparison type mismatch smallint = int	146
TNOU 4 Table with no unique index master..spt_values	master..spt_values
MGTP 3 Grant to public master..spt_values
MGTP 3 Grant to public master..syscolumns
MGTP 3 Grant to public master..sysdatabases
MGTP 3 Grant to public master..syslogins
MGTP 3 Grant to public sybsystemprocs..sp_renamedb
MNER 3 No Error Check should check return value of exec	216
MNER 3 No Error Check should check return value of exec	222
MUCO 3 Useless Code Useless Brackets	51
MUCO 3 Useless Code Useless Brackets	52
MUCO 3 Useless Code Useless Brackets	64
MUCO 3 Useless Code Useless Brackets	77
MUCO 3 Useless Code Useless Brackets	78
MUCO 3 Useless Code Useless Brackets	91
MUCO 3 Useless Code Useless Brackets	105
MUCO 3 Useless Code Useless Brackets	121
MUCO 3 Useless Code Useless Brackets	134
MUCO 3 Useless Code Useless Brackets	155
MUCO 3 Useless Code Useless Brackets	170
MUCO 3 Useless Code Useless Brackets	207
MUCO 3 Useless Code Useless Brackets	211
MUCO 3 Useless Code Useless Brackets	227
MUPK 3 Update column which is part of a PK or unique index name	194
QAFM 3 Var Assignment from potentially many rows	111
QISO 3 Set isolation level	71
QNAJ 3 Not using ANSI Inner Join	145
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered (id, number, colid) Intersection: {id}	112
MSUB 2 Subquery Marker	83
MSUB 2 Subquery Marker	97
MTR1 2 Metrics: Comments Ratio Comments: 60%	33
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 9 = 16dec - 9exi + 2	33
MTR3 2 Metrics: Query Complexity Complexity: 88	33