SQL Server Journey with SQL Authority

Posted in Pinal Dave, SQL, SQL Authority, SQL DMV, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, T SQL, Technology | 58 Comments

58 Responses

1. on January 3, 2008 at 2:36 pm | Reply parikh

   is this possible in sql server 2000. Its urgent so please revert me asap. Any other way to know the same thing in sql server 2000.

   
   
2. on January 5, 2008 at 3:32 pm | Reply SQL SERVER - 2005 - CROSS APPLY Journey to SQL Authority with Pinal Dave

   [...] 4, 2008 by pinaldave Yesterday I wrote article about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query. I had used CROSS APPLY in the query. I got email from one reader asking what is CROSS APPLY. In [...]

   
   
   • on June 9, 2010 at 7:25 pm | Reply srinu

     Hi dave,

     i have a doubt not regarding above query,

     Unfortunately i have deleted one function through Object explorer.
     even i don’t have backup for my database.

     How can i roll back recent deleted functions.

     hope i am clear.

     thanks
     srinu.

     
     
3. on January 10, 2008 at 1:54 pm | Reply Tommelorus

   Useful query. Thanks.

   
   
4. on January 15, 2008 at 3:09 pm | Reply Jon Sayce

   Hi,

   I regularly found myself trying to remember recent queries, so I wrote an add-in to store them:

   http://sqlblogcasts.com/blogs/jonsayce/archive/2008/01/14/Total-SQL-Recall.aspx

   Jon.

   
   
5. on January 15, 2008 at 3:20 pm | Reply pinaldave

   Jon,

   Wonderful Add-in.

   Thank you very much,

   Regards,
   Pinal Dave ( http://www.SQLAuthority.com )

   
   
6. on January 18, 2008 at 3:41 am | Reply BrandonMoser

   Dave, cool script. Anyway (without running profiler constantly) we can go back (say for security reasons) and see what queries were run a day or so ago? You can email directly, too.

   Thanks,
   Brandon

   
   
7. on January 21, 2008 at 7:35 pm | Reply sen

   Can we get the userID under which the SQL statements were executed?

   Thanks
   Sen

   
   
8. on February 5, 2008 at 12:41 pm | Reply craig

   I was curious about this so I tried it & found the results a little difficult to believe. It shows that every fraction of a second, I’m running the create command on a stored procedure that already exists… as if I’m constantly trying to create it..but I think the SP is just executing. Thoughts?

   
   
9. on April 20, 2008 at 9:40 pm | Reply Vaibhav

   Hi,

   I am also interested in tracing out from which host address the statement got executed.

   Thanks,
   Vaibhav.

   
   
10. on May 29, 2008 at 10:53 pm | Reply Jorge

    Hi!!

    This query can be made in SQL-SERVER 2000?

    
    
11. on August 20, 2008 at 3:57 pm | Reply Palani

    Hi

    I was searching so long time after i got your article.

    its really wonderful.

    Thanks & Regds
    Palani

    
    
12. on November 7, 2008 at 6:32 pm | Reply Haroon

    Hi Mate
    It gives last ran queries, If an SP is ran it will give the complete syntax of the SP, But not the execution of it. e.g

    CREATE PROCEDURE [dbo].[spTest]
    @myParam VARCHAR(100)

    BEGIN

    – SOMETHING

    END

    it would be very nice if WE COULD HAVE something like

    exec spTest @myPara = ‘abc’

    I am asking more or less like a SQL Server profiler trace.

    Please contact on lukegaroon@gmail.com

    
    
13. on December 29, 2008 at 2:50 pm | Reply penks

    Nice post. But i wanted to know which alert transaction has been applied on the particular table on which date.

    I tried
    select *
    from ::fn_dblog(null, null)
    where Operation = ‘LOP_MODIFY_COLUMNS’

    but it does not provide information regarding which column altered.

    Can any one help me?

    
    
14. on February 17, 2009 at 4:20 am | Reply Vander

    Can anyone please tell me how to do this in sql 2000. Having problems with “Cross Apply”

    
    
15. on April 1, 2009 at 11:14 pm | Reply Matt

    Great code! For anyone trying to use it make sure it run it against the MASTER database.

    BUT, Is there anyway I can get it to go back farther? It seems to only display records since the last time I booted.

    
    
16. on July 4, 2009 at 12:22 pm | Reply Ankur

    How to rollback last recently ran query in SQL Server. If we had not mentioned rollback to Savepoint or begin Trasaction methods. Is there any alternative.

    
    
17. on July 6, 2009 at 1:05 am | Reply Imran Mohammed

    @Ankur,

    Point in Time Recovery ( If you have your database in Full Recovery Model)

    ~ IM.

    
    
18. on July 8, 2009 at 4:08 pm | Reply Ankur

    Thanks for response

    
    
19. on July 15, 2009 at 9:35 am | Reply Anand

    Can we find out what were last 100 queries that got executed on a perticulare DB. The query that you wrote will return those queries that are in memory.

    
    
20. on October 17, 2009 at 7:00 am | Reply SQL SERVER – Recently Executed T-SQL Query Journey to SQL Authority with Pinal Dave

    [...] 16, 2009 by pinaldave About a year ago, I wrote blog post about SQL SERVER – 2005 – Last Ran Query – Recently Ran Query.  Since, then I have received many question regarding how this is better than fn_get_sql() or DBCC [...]

    
    
21. on November 9, 2009 at 4:24 pm | Reply Vijayakumar

    Hi Pinal,

    It is wonderful……

    thanks a lot….

    
    
22. on November 13, 2009 at 12:29 pm | Reply Mudasser Hassan

    Hi!

    Is there any way to rollback last executed query?

    
    
23. on June 2, 2010 at 11:23 pm | Reply Tas

    Hi
    Can we find out what were last 100 queries that got executed in SQL Server 2000. When I executed same query got error.
    Line 4: Incorrect syntax near ‘APPLY’.
    Thanks

    
    
24. on June 10, 2010 at 8:11 pm | Reply A

    Is there any way to see the last ran queries in MS SQL 2000? I couldn’t find it.

    
    
25. on July 8, 2010 at 12:48 pm | Reply Sathish

    Hi

    One of my table column is updated randomly and the value got changed all of a sudden. But after sometime, it may work fine. The change is constantly happening on a specific column of a specific table. Is there any way to find out which query caused that update?

    
    
26. on August 13, 2010 at 10:11 pm | Reply Seshu

    Hi ,

    How to get last updated row and its values for a particular table .

    Thanks
    Seshu

    
    
27. on December 28, 2010 at 8:28 pm | Reply Rroy

    Hi,
    I ran the code
    SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC

    but got
    Msg 170, Level 15, State 1, Line 3
    Line 3: Incorrect syntax near ‘APPLY’.

    any ideas?
    ran it in management studio

    
    
    • on December 31, 2010 at 4:14 pm | Reply madhivanan

      Note that this will work from version 2005 onwards only

      
      
28. on December 31, 2010 at 5:00 am | Reply Mani

    A very useful query. Will it be possible to filter by machine as well?

    
    
    • on December 31, 2010 at 4:00 pm | Reply madhivanan

      Not possible until you audit it

      
      
29. on January 25, 2011 at 10:59 pm | Reply Tas

    Please let me know how to get last query ran in SQL Server 2000

    
    
30. on February 10, 2011 at 4:40 pm | Reply Abdur Rahman

    thanks…
    penal dave
    how to use
    cross apply in sql

    thanks a lot……

    
    
    • on February 15, 2011 at 9:07 pm | Reply madhivanan

      SQL Server help file has code example for cross aplly. Refer it

      
      
31. on March 24, 2011 at 10:25 am | Reply Doron

    Dave,

    Thank you for the tip about the last ran queries. How ever, how can I view only USERS activity ?? not the system activity or the SQL Agent activity ?

    I know that SQL Server Profiler can do the job but I would like to know the code behind…

    Thanks !

    Doron

    
    
32. on April 4, 2011 at 1:24 pm | Reply Inder

    Hi
    I need to insert last executed query with user but user will come via UI(.net code). Please let me know how can I manage?
    Rrds,
    Inder

    
    
33. on April 23, 2011 at 3:12 am | Reply preeti jaiswal

    how we use sql server2005? where we write query and run it because there is no query analyzer option……. plz tell me the whole process stepwise

    
    
    • on September 13, 2012 at 4:30 pm | Reply Amit Choudhary

      See again there will Sql Management studio

      
      
34. on April 23, 2011 at 3:13 am | Reply preeti jaiswal

    how we use sql server2005? where we write query and run it because there is no query analyzer option……. plz tell me the whole process stepwise

    
    
35. on May 24, 2011 at 6:59 pm | Reply basava

    how to verify last query executed in query analyser

    
    
36. on May 26, 2011 at 8:28 pm | Reply basava

    Hi All,

    I need some information about SQL SERVER “DINALI” and differences Btn 2008 & 2011

    Regards,
    Basava

    
    
37. on July 26, 2011 at 2:15 pm | Reply Purush otta ma

    **********Go for this it will update last ran query with username*********

    SELECT c.session_id, s.host_name, s.login_name, s.status
    , st.text, s.login_time, s.program_name, *
    FROM sys.dm_exec_connections c
    INNER JOIN sys.dm_exec_sessions s ON c.session_id = s.session_id
    CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) AS st
    ORDER BY c.session_id

    
    
38. on July 29, 2011 at 7:05 pm | Reply suba

    Is is possible to get this as Database category ????????

    
    
39. on August 22, 2011 at 11:10 am | Reply AnilJayanti

    Hi All,

    I wrote and tested one complex query 2 days back. today suddenly my system got restarted due to technical issue. So, Now any idea to get my previous complex query… Please help…

    AnilJayanti

    
    
40. on September 8, 2011 at 11:53 am | Reply shankar

    sir i worked in oracale 9i.In tha all query all dispalying in smae window but in microsoft sql it cnt.what to do?
    first time i am working in microsoft sql thats way sir. if i want to display all previous query in same window what to do ?

    
    
41. on September 29, 2011 at 12:47 pm | Reply Vishal

    Hello Pinal,

    I want to track store procedure and its executed time by trigger.
    When the store procedure is executed by application it should insert the store procedure name and its execution time in one table.

    Reply soon…

    
    
42. on February 7, 2012 at 10:47 pm | Reply g2-902125243db7d5e697a20ae0137565ac

    Hello,
    I run the query but the run I did yesterday is not showing … it was run within the “New Query” box interactively… anyway to trace it? to see if it was successfull?
    Thanks,
    Dom

    
    
43. on February 15, 2012 at 4:10 am | Reply JMAX()

    This query was a life saver thanks

    
    
44. on February 18, 2012 at 3:14 pm | Reply Annada Prasad Nanda

    I want all records after today.what is the particular query

    
    
45. on April 4, 2012 at 6:39 pm | Reply Rajesh bhimradia

    hello…
    I have executed a query yesterday, but i don’t know which query i have ran. So, can you tell me where could i got the ran query log in sql server 2008.

    
    
46. on June 1, 2012 at 11:23 pm | Reply Deividi

    Freaking genious buddy.
    You sir saved my bacon :)

    
    
47. on July 5, 2012 at 12:59 pm | Reply Mark Anthony

    Good day, sir.

    I have created a query that makes use of CROSS APPLY. I was able to successfully create it. My problem is that when I tried to integrate it to Delphi dataset, it’s giving me an error when I try to activate the dataset. It seems that Delphi doesn’t want CROSS APPLY because when I try to remove the CROSS APPLY from my SQL code and run it again, it does not give any error at all. By the way, I’m using Delphi 7.

    I hope to read enlightenment. Thank you.

    
    
48. on September 7, 2012 at 6:24 pm | Reply Shaiju CK

    Pinal, Thanks for this query.

    Is it possible to get more than one day’s data for the same. By this query I am getting today’s data. Old executed queries are not coming in the recordset. Is there any way to get it?

    Thanks,
    Shaiju CK

    
    
49. on November 13, 2012 at 1:59 pm | Reply Labinash

    SELECT *
    FROM SystemConfig_Ctl AS D
    CROSS APPLY dbo.Split(D.SysValue,’,') AS ST

    What goes wring in this query

    Thanks
    Labinash

    
    
50. on January 5, 2013 at 7:01 am | Reply SQL SERVER – Weekly Series – Memory Lane – #010 « SQL Server Journey with SQL Authority

    [...] Last Ran Query – Recently Ran Query [...]

    
    
51. on February 6, 2013 at 12:48 pm | Reply Shyam

    In SQL Server how to undo last executed query ?

    
    
    • on February 13, 2013 at 3:44 pm | Reply madhivanan

      If you used transaction, use rollback

      
      
52. on February 21, 2013 at 8:25 am | Reply Satish Mishra

    What if our SQL server gets rebooted?

    
    

Leave a Reply Cancel reply

Enter your comment here...

Fill in your details below or click an icon to log in:

Email (required) (Address never made public)

Name (required)

Website

You are commenting using your WordPress.com account. ( Log Out / Change )

You are commenting using your Twitter account. ( Log Out / Change )

You are commenting using your Facebook account. ( Log Out / Change )

Cancel

Connecting to %s

Notify me of follow-up comments via email.

Notify me of new posts via email.