如何在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?)

我需要检查上次功能何时更改。我知道如何检查创建日期（它位于SQL Server Management Studio的函数属性窗口中）。我发现在SQL Server 2000中无法检查修改日期（请看这篇文章：是否可以确定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?

是否可以在SQL Server 2008中进行检查？ MS是否在系统表中添加了一些允许检查它的新功能？

7 个解决方案

#1

263

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

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

对于过程，函数的类型是FN而不是P.或者您可以在名称列上进行过滤。

#2

Have you tried:

你有没有尝试过：

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

#3

This is the correct solution.

这是正确的解决方案。

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

#4

For SQL 2000 I would use:

对于SQL 2000，我会使用：

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

#5

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

#6

In latest version(2012 or more) we can get modified stored procedure detail by using this query

在最新版本（2012或更高版本）中，我们可以使用此查询来修改存储过程详细信息

SELECT create_date, modify_date, name FROM sys.procedures 
ORDER BY modify_date DESC

#7

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

#1

263

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