在 sql enterprise management 中無法直接修改資料庫名稱, 應該是避免不小心改到資料庫的名稱的一

[tim]

在 sql enterprise management 中無法直接修改資料庫名稱, 應該是避免不小心改到資料庫的名稱的一個設計, 那麼如何修改資料庫名稱呢? 其實很簡單, 有兩個方法:

1. sp_renamedb 利用這個 stored procedure 即可順利 rename, 使用方式如下,

sp_renamedb [ @dbname = ] 'old_name' ,
[@newname =] 'new_name'

2. ALTER DATABASE database MODIFY NAME = new_dbname

使用 alter database 指令也可進行修改, 其實 sp_renamedb 也是呼叫此 alter database 來進行修改, 但是 sp_renamedb 會進行檢查, 以避免不必要的錯誤!

原始的 sp_renamedb 內容如下,

create procedure 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 (4000)

/*
** 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,'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 = 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