This System stored procedure helps to rename the database without using any wizard
Syntax to Use
- exec sp_renamedb 'old Database Name' 'New DataBase Name'
Entire script of the stored procedure will be look like as follows
- USE [TMS]
- GO
-
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER procedure [sys].[sp_renamedb] --- 1996/08/20 13:52
- @dbname sysname, -- old (current) db name
- @newname sysname -- new name we want to call it
- as
- -- Use sp_rename instead.
- declare @objid int -- object id of the thing to rename
- declare @bitdesc varchar(30) -- bit description for the db
- declare @curdbid int -- id of database to be changed
- declare @execstring nvarchar (max)
-
- -- If we're in a transaction, disallow this since it might make recovery impossible.
- set implicit_transactions off
- if @@trancount > 0
- begin
- raiserror(15002,-1,-1,'sys.sp_renamedb')
- return (1)
- end
-
- -- Only the SA can do this.
- if not (is_srvrolemember('dbcreator') = 1)
- begin
- raiserror(15247,-1,-1)
- return (1)
- end
-
- -- Make sure the database exists.
- if not exists (select * from master.dbo.sysdatabases where name = @dbname)
- begin
- raiserror(15010,-1,-1,@dbname)
- return (1)
- end
-
- -- Make sure that the @newname db doesn't already exist.
- if exists (select * from master.dbo.sysdatabases where name = @newname)
- begin
- raiserror(15032,-1,-1,@newname)
- return (1)
- end
-
- -- Check to see that the @newname is valid.
- declare @returncode int
- EXEC @returncode = sys.sp_validname @newname
- if @returncode <> 0
- begin
- raiserror(15224,-1,15,@newname)
- return(1)
- end
-
- -- Don't allow the names of master, tempdb, and model to be changed.
- if @dbname in ('master', 'model', 'tempdb')
- begin
- raiserror(15227,-1,-1,@dbname)
- return (1)
- end
-
- select @execstring = 'ALTER DATABASE '
- + quotename( @dbname , '[')
- + ' MODIFY NAME = '
- + quotename( @newname , '[')
-
- EXEC (@execstring)
-
- if @@error <> 0
- begin
- -- No need to raiserror as the CREATE DATABASE will do so
- return(1)
- end
-
- return (0) -- sp_renamedb