Chapter 1: System Procedures

sp_renamedb

Description

Changes the name of a user database.

Syntax

sp_renamedb dbname, newname

Parameters

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.

Examples

Example 1

Renames the accounting database to financial:

sp_renamedb accounting, financial

Example 2

Renames the database named work, which is a Transact-SQL reserved word, to workdb. This example shows how sp_dboption is used to place the work database in single-user mode before renaming it and restore it to multi-user mode afterward:

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

Usage

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:

Event	Audit option	Command or access audited	Information in extrainfo
38	exec_procedure	Execution of a procedure	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect