需注意,改名时该数据库不能被使用。可用如下方式关闭use master go create proc killspid (@dbname varchar(20)) as begin declare @sql nvarchar(500),@temp varchar(1000) declare @spid int set @sql='declare getspid cursor for select spid from sysprocesses where dbid=db_id('''+@dbname+''')' exec (@sql) open getspid fetch next from getspid into @spid while @@fetch_status =0 begin set @temp='kill '+rtrim(@spid) exec(@temp) fetch next from getspid into @spid end close getspid deallocate getspid end --用法 use master exec killspid '数据库名'
更改数据库的名称。语法
sp_renamedb [ @dbname = ] 'old_name' ,
[ @newname = ] 'new_name'参数
[@dbname =] 'old_name'是数据库的当前名称。old_name 为 sysname 类型,无默认值。[@newname =] 'new_name'是数据库的新名称。new_name 必须遵循标识符规则。new_name 为 sysname 类型,无默认值。返回代码值
0(成功)或非零数字(失败)权限
只有 sysadmin 和 dbcreator 固定服务器角色的成员才能执行 sp_renamedb。示例
下例将数据库 accounting 改名为 financial。EXEC sp_renamedb 'accounting', 'financial'
go
create proc killspid (@dbname varchar(20))
as
begin
declare @sql nvarchar(500),@temp varchar(1000)
declare @spid int
set @sql='declare getspid cursor for
select spid from sysprocesses where dbid=db_id('''+@dbname+''')'
exec (@sql)
open getspid
fetch next from getspid into @spid
while @@fetch_status =0
begin
set @temp='kill '+rtrim(@spid)
exec(@temp)
fetch next from getspid into @spid
end
close getspid
deallocate getspid
end --用法
use master
exec killspid '数据库名'