Monitoring Auditing Active Current Running Processes, TSQL queries in SQL Server using SYSPROCESSES and storing results in table

So, we all know what sysprocesses and sp_who2 do.  These gives us the current transactions/processes running on SQL Server Instance at a given moment of time.

Now, what if on a regular interval bases, I want to capture what all processes are running and what is the Query/Batch being running.

Below is a simple Stored procedure, which makes use of SysProcesses, sys.dm_exec_requests and SYS.DM_EXEC_SQL_TEXT and using this all you need to do is, simply call this Stored Procedure in any of the SQL Server Agent Job on the schedule you want, It retrieves all the data needed and stores in a Table under database specified by you, including the QueryText which was running for a particular process during that time. This data can be very handy for auditing, Monitoring, etc.

Code:

Use MyDatabase
Go
CREATE Procedure usp_ImportTSQL
AS
BEGIN
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TSQLLog]') AND type in (N'U'))
CREATE TABLE TSQLLog
 (
  ID int identity(1,1),
  SPID int,
  [command] varchar(50),
  [blocking_session_id] int,
  [LASTWAITTYPE] varchar(50),
  [DBNAME] varchar(50),
  [QueryText] varchar(max),
  [Batch] varchar(max),
  [CPU] bigint,
  [HOSTNAME] varchar(25),
  [LOGIN_TIME] datetime,
  [LOGINNAME] varchar(50),
  [Status] varchar(25),
  [PROGRAM_NAME] varchar(250),
  [NT_DOMAIN] varchar(50),
  [NT_USERNAME] varchar(50)
 )
INSERT INTO TSQLLog
 (
  SPID,
  [command],
  [blocking_session_id],
  [LASTWAITTYPE],
  [DBNAME],
  [QueryText],
  [Batch],
  [CPU],
  [HOSTNAME],
  [LOGIN_TIME],
  [LOGINNAME],
  [Status],
  [PROGRAM_NAME],
  [NT_DOMAIN],
  [NT_USERNAME]
 )
 SELECT SPID,
 ER.command,ER.blocking_session_id,LASTWAITTYPE,
 DB_NAME(SP.DBID) AS DBNAME,
 SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
   ((CASE ER.statement_end_offset
    WHEN -1 THEN DATALENGTH(est.text)
    ELSE ER.statement_end_offset
    END - ER.statement_start_offset)/2) + 1) AS QueryText,
 TEXT as [Batch],CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
 SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
 FROM MASTER..SYSPROCESSES SP
 INNER JOIN sys.dm_exec_requests ER
 ON sp.spid = ER.session_id
 CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
END
GO