sp_renamedb

sp_renamedb dbname, newname

dbname

is the original name of the database.

newname

is the new name of the database. Database names must conform to the rules for identifiers and must be unique.

sp_renamedb accounting, financial

sp_dboption work, single, true
go
use work 
go
checkpoint 
go
sp_renamedb work, workdb 
go
use master 
go
sp_dboption workdb, single, false 
go
use workdb 
go
checkpoint
go

• sp_renamedb changes the name of a database. You cannot rename system databases or databases with external referential integrity constraints.

• The System Administrator must place a database in single-user mode with sp_dboption before renaming it and must restore it to multi-user mode afterward.

• sp_renamedb fails if any table in the database references, or is referenced by, a table in another database. Use the following query to determine which tables and external databases have foreign key constraints on primary key tables in the current database:

  select object_name(tableid), db_name(frgndbid)
  from sysreferences
  where frgndbid is not null
  

  Use the following query to determine which tables and external databases have primary key constraints for foreign key tables in the current database:

  select object_name(reftabid), db_name(pmrydbid)
  from sysreferences
  where pmrydbid is not null
  

  Use alter table to drop the cross-database constraints in these tables. Then, rerun sp_renamedb.

• When you change a database name:

  • Drop all stored procedures, triggers, and views that include the database name

  • Change the source text of the dropped objects to reflect the new database name

  • Re-create the dropped objects

  • Change all applications and SQL source scripts that reference the database, either in a use database_name command or as part of a fully qualified identifier (in the form dbname.[owner].objectname)

• If you use scripts to run dbcc commands or dump database and dump transaction commands on your databases, be sure to update those scripts.

  WARNING! Procedures, triggers, and views that depend on a database whose name has been changed work until they are re-created. Change the definitions of any dependent objects when you execute sp_renamedb. Find dependent objects with sp_depends.

Permissions

Only a System Administrator can execute sp_renamedb.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

See also

Commands create database

System procedures sp_changedbowner, sp_dboption, sp_depends, sp_helpdb, sp_rename

---

Copyright © 2009. Sybase Inc. All rights reserved.

View this document as PDF