.comment-link {margin-left:.6em;}

kHSw

Wednesday, August 04, 2004

Stored Procedure to disconnect all users from a SQL Server database

This is one of the Stored Procedures I use frequently:

CREATE PROCEDURE sp_kill_database_users @arg_dbname sysname with recompile
AS
-- kills all the users in a particular database
-- dlhatheway/3M, 11-Jun-2000

declare @a_spid smallint
declare @msg varchar(255)
declare @a_dbid int

select @a_dbid = sdb.dbid
from master..sysdatabases sdb
where sdb.name = @arg_dbname

declare db_users insensitive cursor for
select
sp.spid
from master..sysprocesses sp
where sp.dbid = @a_dbid

open db_users

fetch next from db_users into @a_spid
while @@fetch_status = 0
begin
select @msg = 'kill '+convert(char(5),@a_spid)
print @msg
execute (@msg)
fetch next from db_users into @a_spid
end

close db_users
deallocate db_users

GO


1 Comments:

Post a Comment

<< Home


 
Stefanie Worm is het liefste vrouwtje van de wereld.
Melina is de liefste schatsie van de wereld (Erik De Maeyer).