How to check date of last change in stored procedure or function in SQL server

sql server stored procedure change history
how to check modification history of stored procedure in sql server
how to check history of stored procedure in sql server
how to find last modified stored procedure in sql server 2012
how to trace stored procedure changes in sql server
how to find who created a stored procedure in sql server
last modified table in sql server
how to check last modified date of stored procedure in oracle

I need to check when function was changed last time. I know how to check creation date (it is in function properties window in SQL Server Management Studio). I found that in SQL Server 2000 it wasn't possible to check modify date ( look at this post: Is it possible to determine when a stored procedure was last modified in SQL Server 2000?)

Is it possible to check it in SQL Server 2008? Does MS add some new feature in system tables that allow to check it?

SELECT name, create_date, modify_date 
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date DESC

The type for a function is FN rather than P for procedure. Or you can filter on the name column.

How to check date of last change in stored procedure or function in , Health Check · Workshop Question: Write a script to list all the stored procedure modified in last 7 Answer: Here is a simple script when you execute in SQL Server of the stored procedure along with date modified and date created. The modified date is also updated when the SP or function is set to  SQL Query for Stored procedure Modification date and time. SELECT name, create_date, modify_date FROM sys.objects WHERE type = ‘P’ order by modify_date desc

Try this for stored procedures:

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'myProc'

List All the Stored Procedure Modified in Last Few , SQL Server stored procedures can implement vital functions within a database. As you date and the last date when a stored procedure was modified The sys​.procedures catalog view provides a SQL Server specific way of  Find when a SQL Server stored procedure or function was last altered with this script. SELECT specific_catalog, specific_schema, routine_type, routine_name, created,last_altered FROM INFORMATION_SCHEMA.ROUTINES ORDER BY 1 asc, 2 asc, 3 asc, 4 asc

This is the correct solution for finding a function:

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'fn'
AND name = 'fn_NAME'

Find SQL Server Stored Procedure Create, Modified, Last Execution , List User Defined Functions in a SQL Server Database Here's a query for an information_schema view that lists all the stored procedures in a database. The third pane shows the create date and last modified date for  Finding last date compiled for a stored procedure – Learn more on the SQLServerCentral forums SQL Server, MVP, M.Sc (Comp Sci) Change YourDatabase to the database name in question

I found this listed as the new technique

This is very detailed

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' 
order by  LAST_ALTERED desc

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo' 
order by  CREATED desc 


SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo' 
order by  LAST_ALTERED desc

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo' 
order by  CREATED desc 

Find SQL Server User Defined Function Create, Modified, Last , SQL Query for Stored procedure Modification date and time SQL Query for How to check date of last change in stored procedure or function in SQL server  SQL Server DDL Trigger is a special kind of trigger that fires in response to Data Definition Language (DDL) statements – CREATE, ALTER, DROP (Table, Function, Index, Stored Procedures, Views.. etc).

For SQL 2000 I would use:

SELECT name, crdate, refdate 
FROM sysobjects
WHERE type = 'P' 
ORDER BY refdate desc

Finding out who has modified a Stored Procedure on SQL Server , You can look at the default trace and figure this out easily. Try the examples from Jonathan Kehayias. -- Show when was the last time a stored procedure -- named uspMyFourthStoredProcedure was run -- run a stored proc exec uspMyFifthStoredProcedure -- when did it last run select db_name() [database name] ,[schema name] = SCHEMA_NAME([schema_id]) ,o.name ,ps.last_execution_time from sys.dm_exec_procedure_stats ps inner join sys.objects o ON ps.object_id = o.object_id where o.type = 'P' and o.name = 'uspMyFifthStoredProcedure' order by ps.last_execution_time -- wait a minute waitfor delay '00

How to check stored procedure was last modified in sql server 2000 , Despite the column heading of LAST_ALTERED, SQL Server 2000 had no mechanism for storing the date a stored procedure, view, or function  To search for stored procedures that were created or modified on a specific date, you can directly search either the sys.objects and sys.procedures tables. Note: I have written and tested the example queries below in SQL Server 2005 and SQL Server 2008. The syntax for earlier or later platforms may vary.

Get Stored Procedure Last Modified Date or Updated Date in SQL , how to get stored procedure last modified date in SQL server. You can try this query in any given SQL Server database: SELECT name, create_date, modify_date FROM sys.procedures WHERE create_date = '20120927' which lists out the name, the creation and the last modification date - unfortunately, it doesn't record who created and/or modified the stored procedure in question.

The Guru's Guide to SQL Server Stored Procedures, XML, and HTML, Name of user who last changed file $Modtime: $ Last modification date/time of VSS archive $Date: $ Last check in date/time $Header: $ A combination of to encrypt the source to your stored procedures, functions, and similar objects. Question: Write a script to list all the stored procedure modified in last 7 days. Answer: Here is a simple script when you execute in SQL Server Management Studio, it will give you the name of the stored procedure along with date modified and date created.

Comments
  • it seems that certain permissions can cause results to be omitted from this query - even though there are some results that return. We have observed different results based on user authority - but I haven't narrowed it down to which authorities are involved. Long story short: run this query as SA if you can to make sure you are seeing everything.
  • You are indeed correct. msdn.microsoft.com/en-us/library/ms190324%28v=sql.105%29.aspx "In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration."
  • A word of warning. If the OP is referring exclusively to changes to a module made via an ALTER DDL statement, then modify_date can be misleading. For example, if sys.sp_refreshsqlmodule is executed on the module, the modify_date will be changed even though technically the code of the module has not changed.
  • how may get the Specified Database Stored Procedures only?!
  • @im_one After the WHERE line, add AND name = 'specified procedure' where specified procedure is the name of the stored proc you want to find.
  • This should be the accepted answer because this is simple and straightforward.
  • What about 2008..? Isn't that the question..?
  • @NREZ queries on sysobjects are supported on 2008 so this will work
  • refdate is not the last modified date. Check the date difference between sys.object and sysobjects.
  • 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.