sp_audit @option="all", @login_name="all", @object_name = "all", @setting = "on"

sp_audit "all", "all", "all", "on"

sp_audit "all", "user_vasya", "all", "on"

select * from main_table

sp_audit 'cmdtext', 'all', 'all', 'on'

sp_audit "all", "user_vasya", "all", "on"

select * from master..monTables

select * from master..monTables

select * from master..monTables

displog '2008-10-30 12:00:00', '2008-10-30 12:20:00'

create procedure dbo.getlog
/* Пример */
/*exec getlog '2008-10-30 09:00:00', '2008-10-30 10:00:00'*/
@dbeg datetime,
@dend datetime
as
begin
select distinct mstat.StartTime as 'StartTime', 
    mstat.EndTime as 'EndTime',
    mstat.CpuTime,
    msql.SQLText
    from monSysStatement mstat
    inner join monSysSQLText msql
    on mstat.BatchID = msql.BatchID and mstat.KPID = msql.KPID 
    where (mstat.StartTime >= @dbeg and mstat.EndTime <=@dend) and (
        msql.SQLText like 'insert%' or
        msql.SQLText like 'select%' or
        msql.SQLText like 'update%') and (
        not msql.SQLText like '%@%' and 
        not msql.SQLText like '%mon_db%' and
        not msql.SQLText like '%monS%' and
        not msql.SQLText like '%sysob%' and
        not msql.SQLText like '%DBName%' and
        not msql.SQLText like '%master.dbo%' and
        not msql.SQLText like '%db_name%' and
        not msql.SQLText like '%proc_role%' and
        not msql.SQLText like '%select type =%') 
    order by  1 , 2 
end

create procedure dbo.getlog
/* Пример */
/*exec getlog '2008-10-30 09:00:00', '2008-10-30 10:00:00'*/
@dbeg datetime,
@dend datetime
as
begin
select distinct mstat.StartTime as 'StartTime', 
    mstat.EndTime as 'EndTime',
    mstat.CpuTime,
    msql.SQLText as 'Text'
    into #t1
    from monSysStatement mstat
    left join monSysSQLText msql
    on mstat.BatchID = msql.BatchID and mstat.KPID = msql.KPID and mstat.SPID = msql.SPID
    where (mstat.StartTime >= @dbeg and mstat.EndTime <=@dend) and (
        msql.SQLText like 'insert%' or
        msql.SQLText like 'select%' or
        msql.SQLText like 'update%') and (
        not msql.SQLText like '%@%' and 
        not msql.SQLText like '%mon_db%' and
        not msql.SQLText like '%monS%' and
        not msql.SQLText like '%sysob%' and
        not msql.SQLText like '%DBName%' and
        not msql.SQLText like '%master.dbo%' and
        not msql.SQLText like '%db_name%' and
        not msql.SQLText like '%proc_role%' and
        not msql.SQLText like '%select type =%' and
        not lower(msql.SQLText) like '%select user_name()%') 
    order by  1 , 2 

select distinct mstat.StartTime as 'StartTime', 
    mstat.EndTime as 'EndTime',
    mstat.CpuTime,
    msql.PlanText as 'Text'
    into #t2
    from monSysStatement mstat
    left join monSysPlanText msql
    on mstat.BatchID = msql.BatchID and mstat.KPID = msql.KPID and mstat.SPID = msql.SPID and 
        mstat.ProcedureID = msql.ProcedureID and mstat.DBID = msql.DBID and mstat.ContextID = msql.ContextID  
    where (mstat.StartTime >= @dbeg and mstat.EndTime <=@dend) and (
        msql.PlanText like 'insert%' or
        msql.PlanText like 'select%' or
        msql.PlanText like 'update%') and (
        not msql.PlanText like '%@%' and 
        not msql.PlanText like '%mon_db%' and
        not msql.PlanText like '%monS%' and
        not msql.PlanText like '%sysob%' and
        not msql.PlanText like '%DBName%' and
        not msql.PlanText like '%master.dbo%' and
        not msql.PlanText like '%db_name%' and
        not msql.PlanText like '%proc_role%' and
        not msql.PlanText like '%select type =%' and
        not lower(msql.PlanText) like '%select user_name()%') 
    order by  1 , 2 

/* Adaptive Server has expanded all '*' elements in the following statement */ select #t1.StartTime, #t1.EndTime, #t1.CpuTime, #t1.Text                                                                                                                                                                                        from #t1
union
select #t2.StartTime, #t2.EndTime, #t2.CpuTime, #t2.Text from #t2

drop table #t1
drop table #t2
end