How to check date of last change in stored procedure or function in SQL server
Sql Server-2008Stored ProceduresSql Server-2008-R2Sql FunctionSql Server-2008 Problem Overview
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: https://stackoverflow.com/questions/163957/is-it-possible-to-determine-when-a-stored-procedure-was-last-modified-in-sql-serv)
Is it possible to check it in SQL Server 2008? Does MS add some new feature in system tables that allow to check it?
Sql Server-2008 Solutions
Solution 1 - Sql Server-2008
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.
Solution 2 - Sql Server-2008
Try this for stored procedures:
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'myProc'
Solution 3 - Sql Server-2008
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'
Solution 4 - Sql Server-2008
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
Solution 5 - Sql Server-2008
For SQL 2000 I would use:
SELECT name, crdate, refdate
FROM sysobjects
WHERE type = 'P'
ORDER BY refdate desc
Solution 6 - Sql Server-2008
In latest version(2012 or more) we can get modified stored procedure detail by using this query
SELECT create_date, modify_date, name FROM sys.procedures
ORDER BY modify_date DESC
Solution 7 - Sql Server-2008
SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')
AND name = 'dgdsgds'
Solution 8 - Sql Server-2008
You can use this for check modify date of functions
and stored procedures
together ordered by date :
SELECT 'Stored procedure' as [Type] ,name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
UNION all
Select 'Function' as [Type],name, create_date, modify_date
FROM sys.objects
WHERE type = 'FN'
ORDER BY modify_date DESC
or :
SELECT type ,name, create_date, modify_date
FROM sys.objects
WHERE type in('P','FN')
ORDER BY modify_date DESC
-- this one shows type like : FN for function and P for stored procedure
Result will be like this :
Type | name | create_date | modify_date
'Stored procedure' | 'firstSp' | 2018-08-04 07:36:40.890 | 2019-09-05 05:18:53.157
'Stored procedure' | 'secondSp' | 2017-10-15 19:39:27.950 | 2019-09-05 05:15:14.963
'Function' | 'firstFn' | 2019-09-05 05:08:53.707 | 2019-09-05 05:08:53.707