Hi,



I am attempting to create a script that will traverse the
master.dbo.sysprocesses table, to identify a table named "UserDB" then
kill any processes using this database. First I create a temp table,
then I use a while statement to run through the table identifying which
spid's to kill. For testing purposes, I commented out the Exec
(@Sqlstr) line.



Any suggestions on how to make this script work and possibly optimize it
would be greatly appreciated.



Thanks!



Anthony



Script:

------------------------------------------------------------------------
----------

CREATE TABLE #sys_processes_temp (

spid smallint, --The system process ID.

ecid smallint, --The execution context ID of
a given thread associated with a specific SPID.

status nchar(30), --The process status.

loginame nchar(128), --The login name associated with
the particular process.

hostname nchar(128), --The host or computer name for each
process.

dbname nchar(128), --The database used by the process.


cmd nchar(16)

)



DECLARE @sql VARCHAR(200)

SET @sql = 'select spid, ecid, status, loginame = rtrim(loginame),
hostname, dbname = case when dbid = 0 then null when dbid <> 0 then
db_name(dbid) end, cmd from master.dbo.sysprocesses'

INSERT INTO #sys_processes_temp

EXEC(@sql)

SELECT * FROM #sys_processes_temp



DECLARE @sqlstr VARCHAR(200)

WHILE exists (select spid from #sys_processes_temp where dbname =
'UserDB')

begin

DECLARE @spidno INT

select @spidno = spid from #sys_processes_temp

select @sqlstr = 'KILL ' + convert(varchar(10), @spidno)

print 'Killing spid ' + convert(varchar(10), @spidno)

-- Exec (@Sqlstr)

end



DROP TABLE #sys_processes_temp


MS Sql Server LazyDBA home page