database dependencies --audit all tables columns used

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

May 29th, 2015 11:13am

Use Adam's great procedure

/*********************************************************************************************
Who Is Active? v10.00 (2010-10-21)
(C) 2007-2010, Adam Machanic


Updates: http://sqlblog.com/blogs/adam_machanic/archive/tags/who+is+active/default.aspx
"Beta" Builds: http://sqlblog.com/files/folders/beta/tags/who+is+active/default.aspx

License: 
Who is Active? is free to download and use for personal, educational, and internal 
corporate purposes, provided that this header is preserved. Redistribution or sale 
of Who is Active?, in whole or in part, is prohibited without the author's express 
written consent.

Free Windows Admin Tool Kit Click here and download it now
May 31st, 2015 1:52am

Hi ,

I would suggest use CDC (Change data capture).

July 16th, 2015 10:09pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics