DECLARE @sessions TABLE(
[spid]
FLOAT,
[ecid]
FLOAT,
[status]
VARCHAR(1000),
[loginame]
VARCHAR(1000),
[hostname]
VARCHAR(1000),
[blk]
FLOAT,
[dbname]
VARCHAR(1000),
[cmd]
VARCHAR(1000),
[request_id]
FLOAT
)
SET NOCOUNT ON
INSERT INTO @sessions
EXEC sp_who
DECLARE @spid int
DECLARE db_cursor CURSOR FOR
SELECT spid FROM @sessions WHERE dbname = 'xxxxxx' and spid <> @@SPID
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @killStatement varchar(100)
SELECT @killStatement =
'kill '+CONVERT(VARCHAR, @spid)
EXEC (@killStatement)
FETCH NEXT FROM db_cursor INTO
@spid
END
CLOSE db_cursor
DEALLOCATE db_cursor
No comments:
Post a Comment