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

最新推荐文章于 2023-02-16 20:27:12 发布

isoleo

最新推荐文章于 2023-02-16 20:27:12 发布

阅读量3.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： sql server 文章标签： sql server 结束进程

本文链接：https://blog.youkuaiyun.com/isoleo/article/details/45556109

sql server 专栏收录该内容

41 篇文章

订阅专栏

这篇博客提供了一个存储过程`StopLogin`，用于在SQL Server中结束指定数据库的所有进程。如果未指定数据库，则默认结束除`master`数据库外的所有数据库进程。作者通过游标和动态SQL实现该功能，确保所有相关连接被终止。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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