I have used Profiler, Database audit, and also SP_who2 like below :
CREATE TABLE #sp_who2
(
SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT ,
RequestId INT
)
Create Table #SqlStatement (spid int, statement varchar(8000))
create table #temp (x varchar(100), y int, s varchar(1000), id INT IDENTITY (1,1))
INSERT #sp_who2 EXEC sp_who2
Declare @spid varchar(10)
Declare @Statement varchar(8000)
declare @sql varchar(1000)
DECLARE SpidCursor Cursor
FOR Select spid from #sp_who2
OPEN SpidCursor
FETCH NEXT FROM SpidCursor
INTO @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'dbcc inputbuffer (' + @spid + ')'
insert #temp
exec (@sql)
Insert Into #SqlStatement (spid,Statement)
Select @spid, s From #Temp where id = (Select max(id) from #Temp)
FETCH NEXT FROM SpidCursor
INTO @spid
END
Close SpidCursor
Deallocate SpidCursor
Select B.Statement, A.* from #sp_who2 A
LEFT JOIN
#SqlStatement B ON A.spid = B.spid
so , i got SQL text / text data(which include all statements) from Auditing,Profiler, also from SP_who2 ;
nhow How can i get fromm sql text ...
Table_name column_name Select Insert Update Delete Alter Drop
account id 1 1 1 1 1 0
Account Name 1 1 0 0 0 0
Customer Address 1 0 1 0 0 0
OR
Table_name Select Insert Update Delete Alter Drop
account ID Name ID SSN State NULL
Account Name NULL NULL
Customer NAme NULL
some thing like this
can anyone plz help