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?
This question is related to
sql-server-2008
stored-procedures
sql-server-2008-r2
sql-function
SELECT *
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')
AND name = 'dgdsgds'
For SQL 2000 I would use:
SELECT name, crdate, refdate
FROM sysobjects
WHERE type = 'P'
ORDER BY refdate desc
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'
Try this for stored procedures:
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'myProc'
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
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
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
Source: Stackoverflow.com