locked
database dependencies --audit all tables columns used RRS feed

  • Question

  • 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

    Friday, May 29, 2015 3:11 PM

Answers

  • 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.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 31, 2015 5:51 AM

All replies