DatabaseProcApplicationCreatedLinks
sybsystemprocssp_renamedb  14 déc. 14Defects Propagation 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 %2! database."
17    ** 17056, "read only"
18    ** 17168, "low durability"
19    ** 18850, "HA Error: Database '%1!' is a system proxy database. You must execute sp_renamedb on the primary server first."
20    ** 18851, "HA Error: You must be in the master database in order to run '%1!' against a system proxy database."
21    ** 18843, "Please check the System Administration Guide to determine how to %1! the corresponding proxy or real database on the companion server '%2!'."
22    */
23    
24    
25    
26    /*
27    ** IMPORTANT NOTE:
28    ** This stored procedure uses the built-in function db_id() in the
29    ** where clause of a select query. If you intend to change this query
30    ** or use the object_id() or db_id() builtin in this procedure, please read the
31    ** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
32    ** pertaining to object-id's and db-id's outlined there, are followed.
33    */
34    
35    create or replace procedure sp_renamedb
36        @dbname sysname(30), /* old (current) db name */
37        @newname sysname(30) /* new name we want to call it */
38    as
39    
40        declare @msg varchar(1024)
41            , @bitdesc varchar(30) /* bit description for the db */
42            , @HA_CERTIFIED tinyint /* Is the SP HA certified ? */
43            , @retstat int
44            , @maxobjlen int
45            , @rollback int
46            , @nullarg char(1)
47            , @dummy int
48            , @status int
49            , @gp_enabled int
50            , @dbid int /* dbid for database */
51    
52        select @HA_CERTIFIED = 0
53    
54    
55    
56        /* check to see if we are using HA specific SP for a HA enabled server */
57        exec @retstat = sp_ha_check_certified 'sp_renamedb', @HA_CERTIFIED
58        if (@retstat != 0)
59            return (1)
60    
61        /*
62        **  If we're in a transaction, disallow this since it might make recovery
63        **  impossible.
64        */
65        if @@trancount > 0
66        begin
67            /*
68            ** 17260, "Can't run %1! from within a transaction." 
69            */
70            raiserror 17260, "sp_renamedb"
71            return (1)
72        end
73        else
74        begin
75            set chained off
76        end
77    
78        set transaction isolation level 1
79    
80        /* 
81        ** If granular permissions is not enabled then sa_role is required.
82        ** If granular permissions is enabled then the permission 'own database' is
83        ** required.  proc_role and proc_auditperm will also do auditing
84        ** if required. Both will also print error message if required.
85        */
86    
87        select @nullarg = NULL
88        execute @status = sp_aux_checkroleperm "sa_role", "own database",
89            @dbname, @gp_enabled output
90    
91        /* For Auditing */
92        if (@gp_enabled = 0)
93        begin
94            if (proc_role("sa_role") = 0)
95                return (1)
96        end
97        else
98        begin
99            select @dummy = proc_auditperm("own database", @status, @dbname)
100       end
101   
102       if (@status != 0)
103           return (1)
104   
105       /*
106       **  Make sure the database exists.
107       */
108       if not exists (select *
109               from master.dbo.sysdatabases
110               where name = @dbname)
111       begin
112           /*
113           ** 17590, "The specified database does not exist."
114           */
115           raiserror 17590
116           return (1)
117       end
118   
119       /*
120       **  Make sure that the @newname db doesn't already exist.
121       */
122       if exists (select *
123               from master.dbo.sysdatabases
124               where name = @newname)
125       begin
126           /*
127           ** 17791, "A database with the new name already exists."
128           */
129           raiserror 17791
130           return (1)
131       end
132   
133       /*
134       **  Check to see that the @newname is valid.
135       */
136       select @maxobjlen = length from master.dbo.syscolumns
137       where id = object_id("master.dbo.sysdatabases")
138           and name = 'name'
139   
140       if valid_name(@newname, @maxobjlen) = 0
141       begin
142           /*
143           ** 17240, "'%1!' is not a valid name."
144           */
145           raiserror 17240, @newname
146           return (1)
147       end
148   
149       /*
150       **  Don't allow the names of master, tempdb, and model to be changed.
151       */
152       if @dbname in ("master", "model", "tempdb", "sybsecurity", "sybsystemprocs", "mounted_sybsystemprocs")
153       begin
154           /*
155           ** 17792, "The databases master, model, tempdb, sybsecurity, 
156           ** sybsystemprocs and mounted_sybsystemprocs cannot be renamed."
157           */
158           raiserror 17792
159           return (1)
160       end
161   
162   
163       /* 
164       ** Check single user bit (4096) 
165       ** Database must be in single user mode to necessitate the rid update in the
166       ** database's DBTABLE
167       */
168       select @bitdesc = null
169       select @bitdesc = v.name
170       from master.dbo.spt_values v, master.dbo.sysdatabases d
171       where d.dbid = db_id(@dbname)
172           and v.type = "D"
173           and d.status & v.number = 4096
174       if @bitdesc is null
175       begin
176           /*
177           ** 17793, "System Administrator (SA) must set database '%1!' to single-user mode with sp_dboption before using '%2!'."
178           */
179           raiserror 17793, @dbname, "sp_renamedb"
180           return (1)
181       end
182   
183   
184   
185       /* 
186       ** This stored procedure can not be executed from a low durablity database
187       ** as the changes made by follwoing 'dbcc chgdbname' can not be undone
188       ** as the execution will not come back to stored procedure when error 
189       ** 3952 happens. So we check if we are in a low durability database before
190       ** starting execution of the stored procedure.
191       */
192       if db_attr(db_name(), "durability") != 'full'
193       begin
194           /*
195           ** 17902 "You cannot run stored procedure '%1!' from a %2! database."
196           ** 17168 "low durability" 
197           */
198           exec sp_getmessage 17168, @msg output
199           raiserror 17902, "sp_renamedb", @msg
200           return (1)
201       end
202   
203       /*
204       ** This stored procedure can be excuted only from master database
205       ** for read only database.
206       */
207       select @dbid = dbid from master.dbo.sysdatabases
208       where name = @dbname
209           and (status & 1024) = 1024
210   
211       if (@dbid is not null)
212       begin
213           if (db_name() != "master")
214           begin
215               /*
216               ** 17902 "You cannot run stored procedure '%1!' from a %2! database."
217               ** 17056 "read only"
218               */
219               exec sp_getmessage 17056, @msg output
220               raiserror 17902, "sp_renamedb", @msg
221               return (1)
222           end
223       end
224   
225       /*
226       **  Update the dbinfo in the sysindexes row for syslogs of the database
227       **  whose name is being changed. Also the dbtable structure for the db
228       **  in question is updated with the new name.
229       **
230       **  NOTE: the following dbcc command relies on the above commands executing.
231       **	  Using this command outside of this procedure can cause a host of
232       **  	  perfidious problems.
233       */
234       dbcc chgdbname(@dbname, @newname)
235   
236       if @@error = 0
237       begin
238           select @rollback = 1
239           begin tran rename_db
240           update master.dbo.syslogins
241           set dbname = @newname
242           where dbname = @dbname
243           if @@error = 0
244           begin
245               update master.dbo.sysdatabases
246               set name = @newname
247               where name = @dbname
248               if @@error = 0
249               begin
250                   commit tran rename_db
251                   set @rollback = @@error
252               end
253           end
254   
255           if @rollback != 0
256           begin
257               rollback tran rename_db
258               dbcc chgdbname(@newname, @dbname)
259               return (1)
260           end
261       end
262       else
263           return (1)
264   
265       /*
266       ** 17794, "Database is renamed and in single-user mode."
267       */
268       exec sp_getmessage 17794, @msg output
269       print @msg
270   
271       /*
272       ** 17795, "System Administrator (SA) must reset it to multi-user mode with sp_dboption."
273       */
274       exec sp_getmessage 17795, @msg output
275       print @msg
276   
277   
278   
279       return (0)
280   


exec sp_procxmode 'sp_renamedb', 'AnyMode'
go

Grant Execute on sp_renamedb to public
go
DEFECTS
 QJWI 5 Join or Sarg Without Index 173
 MCTR 4 Conditional Begin Tran or Commit Tran 239
 MCTR 4 Conditional Begin Tran or Commit Tran 250
 MTYP 4 Assignment type mismatch @bitdesc: varchar(30) = varchar(255) 169
 QPUI 4 Join or Sarg with Un-Rooted Partial Index Use SARG Candidate index: spt_values.spt_valuesclust clustered
(number, type)
Intersection: {type}
172
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 171
 QTYP 4 Comparison type mismatch smallint = int 171
 TNOU 4 Table with no unique index master..spt_values master..spt_values
 MAW1 3 Warning message on %name% master..syscolumns.id: Warning message on syscolumns 137
 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 198
 MNER 3 No Error Check should check return value of exec 219
 MNER 3 No Error Check should check return value of exec 268
 MNER 3 No Error Check should check return value of exec 274
 MUCO 3 Useless Code Useless Brackets 58
 MUCO 3 Useless Code Useless Brackets 59
 MUCO 3 Useless Code Useless Brackets 71
 MUCO 3 Useless Code Useless Brackets 92
 MUCO 3 Useless Code Useless Brackets 94
 MUCO 3 Useless Code Useless Brackets 95
 MUCO 3 Useless Code Useless Brackets 102
 MUCO 3 Useless Code Useless Brackets 103
 MUCO 3 Useless Code Useless Brackets 116
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 146
 MUCO 3 Useless Code Useless Brackets 159
 MUCO 3 Useless Code Useless Brackets 180
 MUCO 3 Useless Code Useless Brackets 200
 MUCO 3 Useless Code Useless Brackets 211
 MUCO 3 Useless Code Useless Brackets 213
 MUCO 3 Useless Code Useless Brackets 221
 MUCO 3 Useless Code Useless Brackets 259
 MUCO 3 Useless Code Useless Brackets 263
 MUCO 3 Useless Code Useless Brackets 279
 MUPK 3 Update column which is part of a PK or unique index name 246
 QAFM 3 Var Assignment from potentially many rows 136
 QISO 3 Set isolation level 78
 QNAJ 3 Not using ANSI Inner Join 170
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: syscolumns.csyscolumns unique clustered
(id, number, colid)
Intersection: {id}
137
 VNRD 3 Variable is not read @nullarg 87
 VNRD 3 Variable is not read @dummy 99
 MSUB 2 Subquery Marker 108
 MSUB 2 Subquery Marker 122
 MTR1 2 Metrics: Comments Ratio Comments: 57% 35
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 22dec - 11exi + 2 35
 MTR3 2 Metrics: Query Complexity Complexity: 103 35

DATA PROPAGATION detailed
ColumnWritten To
@newnamesysdatabases.name   syslogins.dbname   °.procid   sp_dropdevice_rset_001.database_name sp_dropdevice_rset_002.database_name sp_dropdevice_rset_003.sqlbNoName2 sp_checknames_rset_010.name sp_checkreswords_rset_006.sqlbNoName47 sp_config_rep_agent_rset_003.List of Options sp_downgrade_esd_rset_001.check these databases
sp_downgrade_rset_001.dbid °.name sp_downgrade_rset_002.Investigate these databases: sp_downgrade_rset_003.Investigate these databases: sp_downgrade_rset_004.check these databases sp_downgrade_rset_005.check these databases sp_downgrade_rset_006.check these databases sp_downgrade_rset_007.pll_ntas_in_log sp_dropdevice_rset_001.database_name sp_dropdevice_rset_002.database_name
sp_dropdevice_rset_003.sqlbNoName66 sp_jdbc_getcatalogs_rset_001.TABLE_CAT sp_odbc_databases_rset_001.database_name sp_odbc_tables_rset_003.TABLE_CAT sp_oledb_databases_rset_001.CATALOG_NAME sp_oledb_databases_rset_002.CATALOG_NAME sp_oledb_statistics_rset_001.TABLE_CATALOG sp_oledb_tables_rset_001.TABLE_CATALOG sp_oledb_tables_rset_002.TABLE_CATALOG sp_oledb_views_rset_001.TABLE_CATALOG
sp_tables_rset_003.table_qualifier

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   reads table master..sysmessages (1)  
reads table master..spt_values (1)  
reads table master..syscolumns (1)  
calls proc sybsystemprocs..sp_ha_check_certified  
   reads table tempdb..sysobjects (1)  
writes table master..syslogins (1)  
calls proc sybsystemprocs..sp_aux_checkroleperm  
   reads table master..sysconfigures (1)  
   reads table master..syscurconfigs (1)  
read_writes table master..sysdatabases (1)