select count(*) from master.dbo.sysprocesses

select db_name(dbid) dbName,user_name(uid) [user] from sys.sysprocesses

select COUNT(*) from sys.sysprocesses where db_name(dbid) = @dbId

-- 查询连接到指定库的数量  @dbId [指定的库名]

select distinct user_name(uid) [user] from sys.sysprocesses where db_name(dbid) = @dbId

-- 查询连接到指定库的user  @dbId [指定的库名]

-- 存储过程

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    DROP PROCEDURE [dbo].[p_killspid]

go

create   proc   p_killspid

@dbname   varchar(200) --要关闭进程的数据库名

as

declare   @programName     nvarchar(200),

@spid   nvarchar(20)   

declare   cDblogin   cursor   for

select   cast(spid   as   varchar(20))  AS spid   from   master..sysprocesses   where   dbid=db_id(@dbname)

open   cDblogin

fetch   next   from   cDblogin   into   @spid

while   @@fetch_status=0

begin

--防止自己终止自己的进程

--否则会报错不能用KILL 来终止您自己的进程。

IF  @spid <> @@SPID

    exec( 'kill   '+@spid)

fetch   next   from  cDblogin   into   @spid

end

close   cDblogin

deallocate   cDblogin

go   

-- 执行

exec   p_killspid     'your database'  

DECLARE @temp NVARCHAR(20)

DECLARE myCurse CURSOR

FOR

  SELECT  spid

  FROM    sys.sysprocesses

  WHERE   dbid = DB_ID('your database')

OPEN myCurse

FETCH NEXT FROM myCurse INTO @temp

WHILE @@FETCH_STATUS = 0

  BEGIN

    EXEC ('kill '+@temp)

    FETCH NEXT FROM myCurse INTO @temp

  END

CLOSE myCurse

DEALLOCATE myCurse