删除所有链接\批量删除DB\查询某个用户进程ID执行的操代码,MSSQL代码库,德仔网

代码语言

代码分类

【MSSQL】删除所有链接\批量删除DB\查询某个用户进程ID执行的操

作者:Dezai.CN / 发布于2013/3/26/ 865


	--查看某用户的操作
	Create PROCEDURE pr_who_operation
	( @SessionID int = NULL )
	AS

	BEGIN
	 SELECT
	 er.session_id as SPID,
	 ses.status as Status,
	 ses.login_name asLogin,
	 ses.host_name as Host,
	 er.blocking_session_id as BlkBy,
	 DB_Name(er.database_id) as DBName,
	 er.command as CommandType,
	 st.text as SQLStatement,
	 OBJECT_NAME(st.objectid) as ObjectName,
	 er.cpu_time as CPUTime,
	 er.logical_reads + er.reads as IOReads,
	 er.writes as IOWrites,
	 er.last_wait_type as LastWaitType,
	 er.start_time as StartTime,
	 con.net_transport as Protocol,
	 con.num_writes as ConnectionWrites,
	 con.num_reads as ConnectionReads,
	 con.client_net_address as ClientAddress,
	 con.auth_scheme as Authentication
	 FROM sys.dm_exec_requests er
	 OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
	 LEFT JOIN sys.dm_exec_sessions ses
	 ON ses.session_id = er.session_id
	 LEFT JOIN sys.dm_exec_connections con
	 ON con.session_id = ses.session_id
	 WHERE er.session_id > 50
	 AND @SessionID IS NULL OR er.session_id = @SessionID
	 ORDER BY
	 er.blocking_session_id DESC,er.session_id asc

	END

	

	---删除所有链接

	CREATE Proc pr_Kill_AllProcessInDB
	@DbName VarChar(100)
	as
	if db_id(@DbName) = Null
	 begin
	 Print 'DataBase dose not Exist'
	 end

	else
	
	 Begin
	 Declare @spId Varchar(30)
	 DECLARE TmpCursor CURSOR FOR
	 Select 'Kill ' + convert(Varchar, spid) as spId
	 from master..SysProcesses
	 where db_Name(dbID) = @DbName
	 and spId <> @@SpId
	 and dbID <> 0
	 OPEN TmpCursor
	 FETCH NEXT FROM TmpCursor
	 INTO @spId
	 WHILE @@FETCH_STATUS = 0
	 BEGIN
	 Exec (@spId)
	 FETCH NEXT FROM TmpCursor
	 INTO @spId
	 END
	 CLOSE TmpCursor
	 DEALLOCATE TmpCursor
	 end
	

	---删除所有这些DB

	select 'drop database ['+name+']' from sys.databases where name like '7b_%';--最后执行得到的SQL即可

试试其它关键字

　删除所有链接　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)