sp_renamedb

Changes the name of a user database.

Syntax

sp_renamedb dbname, newname

Parameters

Examples

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.

SettingDescription
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:

InformationValues
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

Related reference
sp_changedbowner
sp_dboption
sp_depends
sp_helpdb
sp_rename