SQL Server session kill

So, I had an issue today where I wanted to boot out all the user sessions from a specific database and wanted to do so in script.  I'm sure there is a more elegant way to do this, but this works OK:

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

SQL Server sortable sp_who

Just a little helper to allow filtering and sorting of the SQL Server sp_who standard output:


declare @myTab 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
)


insert into @myTab
exec sp_who


select * from @myTab
where dbname not in (
'master',
'msdb',
'tempdb'
)
order by dbname