Create a storage step under the master database such as the following:
Createproc Killspid (@dbnamevarchar( -))
as
begin
Declare@sqlnvarchar( -)
Declare@spidint
Set@sql='declare getspid cursor for select spid from sysprocesses where dbid in (select dbid from sysdatabases where Name='+@dbname+')'
exec(@sql)
Open Getspid
Fetch Next from Getspid into@spid
while @ @fetch_status<>-1
begin
exec('Kill'+@spid)
Fetch Next from Getspid into@spid
End
Close Getspid
deallocateGetspid
End
--How to use
Use master
exec Killspid " "Database name" "
Single-pin nesting
With three single-cited functions, the outermost single-quote indicates Vchar. The second single argument is escaped. The innermost single argument is the single argument in the string. declare @dbname nvarchar ($)
Set @dbname = ' ZCC '--The resulting variable is ' ZCC '. Set @dbname = ' ZCC '--The resulting variable is ZCC
A "No exclusive access" workaround appears for restoring the database (stored procedure SQL Server that kills database connections)