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

Question

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

User · Answer

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

User · Answer

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

User · Answer

Try this for stored procedures   SELECT name  create date  modify date FROM sys objects WHERE type    P  AND name    myProc

User · Answer

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

User · Answer

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

User · Answer

For SQL 2000 I would use   SELECT name  crdate  refdate  FROM sysobjects WHERE type    P   ORDER BY refdate desc

User · Answer

SELECT   FROM sys objects WHERE type IN   FN    IF    TF    AND name    dgdsgds

User · Answer

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

[sql-server-2008] How to check date of last change in stored procedure or function in SQL server

Examples related to sql-server-2008

Examples related to stored-procedures

Examples related to sql-server-2008-r2

Examples related to sql-function