1 Create Proc Sp_KillAllProcessInDB
2
3 @DbName VarChar(100)
4
5 as
6 if db_id(@DbName) = Null
7 begin
8 Print 'DataBase DOSe not Exist'
9 end
10 else
11
12 Begin
13 Declare @spId Varchar(30)
14
15 DECLARE TmpCursor CURSOR FOR
16 Select 'Kill ' + convert(Varchar, spid) as spId
17 from master..SysProcesses
18 where db_Name(dbID) = @DbName
19 and spId <> @@SpId
20 and dbID <> 0
21 OPEN TmpCursor
22
23 FETCH NEXT FROM TmpCursor
24 INTO @spId
25
26 WHILE @@FETCH_STATUS = 0
27
28 BEGIN
29
30 Exec (@spId)
31
32 FETCH NEXT FROM TmpCursor
33 INTO @spId
34
35 END
36
37
38 CLOSE TmpCursor
39 DEALLOCATE TmpCursor
40
41 end
42
43 GO
44 --To Execute
45 Exec dbo.Sp_KillAllProcessInDB 'DBname'