sql server 结束指定数据库所有进程

来源：互联网发布：会计网络在线课程编辑：程序博客网时间：2024/06/05 05:52

USE [master]
GO
/****** Object: StoredProcedure [dbo].[StopLogin] Script Date: 2015/5/7 9:15:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 李晓蒙
-- Create date: 2015-05-05
-- Description: 结束指定数据库的所有进程，如未指定，则结束掉除master库以外所有库进程
-- =============================================
ALTER PROCEDURE [dbo].[StopLogin] @Dname VARCHAR(50)
AS
DECLARE @name VARCHAR(50) ,
@s VARCHAR(1000)
BEGIN
IF ( @Dname = '' )
BEGIN
DECLARE DataName CURSOR
FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ( 'master' )
OPEN DataName
FETCH NEXT FROM DataName
INTO @name
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
DECLARE tb CURSOR local
FOR
SELECT N'kill ' + CAST(spid AS VARCHAR)
FROM master..sysprocesses
WHERE dbid = DB_ID(@name)
OPEN tb
FETCH NEXT FROM tb INTO @s
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@s)
FETCH NEXT FROM tb INTO @s
END
CLOSE tb
DEALLOCATE tb

FETCH NEXT FROM DataName
INTO @name
END
CLOSE DataName
DEALLOCATE DataName
END
ELSE
BEGIN
DECLARE tb CURSOR local
FOR
SELECT N'kill ' + CAST(spid AS VARCHAR)
FROM master..sysprocesses
WHERE dbid = DB_ID(@Dname)
OPEN tb
FETCH NEXT FROM tb INTO @s
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@s)
FETCH NEXT FROM tb INTO @s
END
CLOSE tb
DEALLOCATE tb
END
END

0 0