sp_renamedb

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

There are additional considerations when using sp_renamedb:

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 with a name that 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.

See also create database in Reference Manual: Commands.

Permissions

The permission checks for sp_renamedb differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `own database` privilege on the database.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

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

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	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