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

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.

---

Try this for stored procedures:

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

---

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'

---

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 

---

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

---

For SQL 2000 I would use:

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

---

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

---

SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF') 
AND name = 'dgdsgds'

---

SELECT name, created_at, updated_at 
FROM table_name.column_name
WHERE type = 'soemthing'
ORDER BY updated_at DESC ;

I hope This will help you