SQL-查看用户使用情况

USE master 
SET NOCOUNT ON
  if exists(select 1 from sysobjects where name like 'Navy_User_Info_dbcc') DROP TABLE Navy_User_Info_dbcc
  CREATE TABLE Navy_User_Info_dbcc
  (
    EventType    nvarchar(30) NULL,
    Parameters   int NULL,
    EventInfo    nvarchar(255) NULL,
  ) ON [PRIMARY]

  if exists(select 1 from sysobjects where name like 'Navy_User_Info') DROP TABLE Navy_User_Info
  CREATE TABLE Navy_User_Info
  (
    [createtime] [datetime] NULL ,
    [spid] [smallint] NOT NULL ,
    [tmLogin] [datetime] NOT NULL ,
    [sLogin] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
    [netlib] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
    [address] [nchar] (12) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [computer] [varchar] (200) COLLATE Chinese_PRC_CI_AS NULL ,
    [pid] [nchar] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [pidDesc] [nchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [sSQL] [varchar] (300) COLLATE Chinese_PRC_CI_AS NULL
  ) ON [PRIMARY]

  INSERT Navy_User_Info
  SELECT getdate(), spid
         , login_time, rtrim(cast(loginame as varchar(200)))
         , cast(net_library as varchar(200)), net_address, rtrim(cast(hostname as varchar(200))), hostprocess, rtrim(program_name), ''
    FROM sysprocesses
   ORDER BY hostname, loginame, net_library, hostprocess

  DECLARE curSPID SCROLL CURSOR FOR SELECT cast(spid as varchar(20)) FROM Navy_User_Info
  DECLARE @nRowCount int
  DECLARE @nRowIndex int

  DECLARE @sSpid as int
  DECLARE @hHandle binary(20)
  DECLARE @sCmd varchar(200)
  DECLARE @sSQL varchar(8000)

  OPEN curSPID
  SET @nRowCount=@@CURSOR_ROWS
  SET @nRowIndex=0
  WHILE ((@nRowIndex=0 AND @nRowCount>0)  OR  (@nRowIndex<@nRowCount AND @@fetch_status=0))
  BEGIN
    FETCH NEXT FROM curSPID INTO @sSpid
    SET @nRowIndex=@nRowIndex+1

    SELECT @sSQL = ''
    IF @sSpid not in ('1','2','3','4','7')
    BEGIN
      DELETE FROM Navy_User_Info_dbcc
      INSERT INTO Navy_User_Info_dbcc EXEC ('DBCC INPUTBUFFER('+@sSpid+')')
      SELECT @sSQL = cast(EventInfo as varchar(8000)) FROM Navy_User_Info_dbcc
    END
    UPDATE Navy_User_Info SET sSQL = @sSQL WHERE spid = @sSpid
  END
  CLOSE curSPID
  DEALLOCATE curSPID

  SELECT * FROM Navy_User_Info order by computer, sLogin, netlib, pid
  DROP TABLE Navy_User_Info_dbcc
  --DROP TABLE Navy_User_Info
RETURN