Ms sql stored procedure update date

In this section, we will discuss how you can check the last execution time of a stored procedure. When you execute any stored procedure or function in SQL Server, the entry is temporarily stored in the cache. You can access the last execution time of a stored procedure in the cache. The sys. But, this method is not reliable. Because you can only get the execution history of the cached objects only.

If the object is removed from the cache, you will not be able to access the execution history. However, if you want a permanent solution, you can implement your own logic. You can create a log table for storing the execution history of stored procedures. You can put an Insert statement at the end of every stored procedure to insert the name of the stored procedure and the current date and time. For example, if you have created a table as StoreLog , you can put an Insert statement like the following code at the end of every stored procedure:.

Thus, you might have learned various methods to track the execution history of stored procedures. At the end of this article, you might be aware of various methods that you can use to find the information about the stored procedures in your database. Notice that different methods of specifying the parameter values are shown. Skip to main content. This browser is no longer supported.

Download Microsoft Edge More info. Contents Exit focus mode. Is this page helpful? Please rate your experience Yes No. The only reliable way to find the schema of an object is to query the sys. Notice that the results exactly match those from the sys. Next, lets switch our focus to discovering the T-SQL code for a stored procedure.

The T-SQL Definition column is added to a preceding query design for listing stored procedures by their database name, schema name, and object name. It is for a stored procedure named uspMyFourthStoredProcedure. You can specify the code for a SQL Server stored procedure with either a create proc or an alter proc statement.

A commented line of code shows how to restrict the result set to a single row based on the name for a specific stored procedure. The following screen shot shows a view from the Editor tab of the defining T-SQL code copied from the preceding screen shot for the stored procedure named uspMySecondStoredProcedure. Two aspects of a stored procedure that you can track are when it was initially created and when it was last modified.

You create a stored procedure with a create proc statement. The following script shows how to query the sys. The script below executes the altered version of the uspMyFifthStoredProcedure stored procedure from the preceding example twice. After each time the stored procedure is run, the script queries an inner join of the sys. The excerpt contains four panes.

Thank you so much for the thoughts and extra digging. I dug through some of the provided links I greatly appreciate most of what Brent Ozar writes, when it's not too deep for my pay grade and appreciate them all.

I am a developer, so I agree that SQL can be a tool or a programming language, or even an entire development environemnt you can do a lot and never leave Management Studio. You can do a lot in SQL Server with T-SQL, but sometimes you need to get down to SQL Server internals, such as memory management, memory grants, and even reconfigurations that can impact memory use and query cache contents.

I lump these together as administrative topics. A word of warning. For example, if sys. TylerH This should be the accepted answer because this is simple and straightforward. Satpal k 12 12 gold badges silver badges bronze badges. Simon Simon 51 1 1 silver badge 1 1 bronze badge. What about ? Isn't that the question..? NREZ queries on sysobjects are supported on so this will work — Paul. Check the date difference between sys.

Renats Stozkovs 2, 10 10 gold badges 21 21 silver badges 24 24 bronze badges. Kris K Kris K 11 1 1 bronze badge. May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post.