CREATE PROCEDURE SP_CloseDBConnect
|
@Dname varchar(50)
|
AS
|
BEGIN
|
DECLARE @name varchar(50), @s varchar(1000)
|
|
IF (@Dname = '')
|
BEGIN
|
DECLARE DataName CURSOR FOR SELECT name FROM sys.databases WHERE name not in ('master');
|
|
OPEN DataName
|
FETCH NEXT FROM DataName INTO @name
|
WHILE (@@FETCH_STATUS = 0)
|
BEGIN
|
DECLARE tb CURSOR local FOR SELECT N'kill '+CAST(spid AS varchar) FROM master..sysprocesses WHERE dbid=db_id(@name)
|
|
OPEN tb
|
FETCH next FROM tb INTO @s
|
WHILE @@FETCH_STATUS=0
|
BEGIN
|
EXEC(@s)
|
FETCH NEXT FROM tb INTO @s
|
END
|
CLOSE tb
|
DEALLOCATE tb
|
|
FETCH NEXT FROM DataName INTO @name
|
END
|
CLOSE DataName
|
DEALLOCATE DataName
|
END
|
ELSE
|
BEGIN
|
DECLARE tb CURSOR local FOR SELECT N'kill '+CAST(spid AS varchar) FROM master..sysprocesses WHERE dbid=db_id(@Dname)
|
|
OPEN tb
|
FETCH next FROM tb INTO @s
|
WHILE @@FETCH_STATUS=0
|
BEGIN
|
EXEC(@s)
|
FETCH NEXT FROM tb INTO @s
|
END
|
CLOSE tb
|
DEALLOCATE tb
|
END
|
END
|
