none
20 executive orders RRS feed

  • Question

  • How to write a query that shows the last 20 executable queries in the database, such as insert update delete , procedure 

    Executive procedures are very important


    Pivot

    Tuesday, July 23, 2019 12:01 PM

All replies

    • Edited by SQLNeophyte Tuesday, July 23, 2019 12:06 PM
    Tuesday, July 23, 2019 12:06 PM
  • By default SQL Server don't log DML queries, you have to implement you own Audit.

    The closest is the SP execution statistic: Stored Procedure Execution Statistics


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, July 23, 2019 12:06 PM
  • Hi,

    You can try this query :

    SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery 
    CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql 
    ORDER BY execquery.last_execution_time DESC 


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    Tuesday, July 23, 2019 12:07 PM
  • I can not see these commands that I execute, the commands my users enter in the database, just like that query.

    Pivot

    Wednesday, July 24, 2019 5:36 AM
  • Hi MohammadBayat,

    Hope this could help: 

    set transaction isolation level read uncommitted
    select top 50
        creation_time,
        last_execution_time,
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.statement_start_offset)/2  ))
        end as query_text,
        db_name(st.dbid)as db_name,
        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
    FROM sys.dm_exec_query_stats  qs
         cross apply sys.dm_exec_sql_text(sql_handle) st
    ORDER BY db_name, object_name
    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 24, 2019 6:11 AM
  • sys.dm_exec_query_stats  is not that reliable, especially not for ad hoc queries: sys.dm_exec_query_stats (Transact-SQL) => "An initial query of sys.dm_exec_query_stats might produce inaccurate results if there is a workload currently executing on the server"

    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    Wednesday, July 24, 2019 6:45 AM