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?

score 456 · Accepted Answer · edited Aug 29 '18 at 13:02

456

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.

edited Aug 29 '18 at 13:02

Adrian

7,745
5
28
28

answered Apr 07 '11 at 10:00

Chris Diver

19,362
4
47
58

5

it seems that certain permissions can cause results to be omitted from this query - even though there are some results that return. We have observed different results based on user authority - but I haven't narrowed it down to which authorities are involved. Long story short: run this query as SA if you can to make sure you are seeing everything. – Ryan Guill Jun 25 '12 at 14:33
2

You are indeed correct. http://msdn.microsoft.com/en-us/library/ms190324%28v=sql.105%29.aspx "In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration." – Chris Diver Jun 26 '12 at 08:07
2

A word of warning. If the OP is referring exclusively to changes to a module made via an ALTER DDL statement, then `modify_date` can be misleading. For example, if `sys.sp_refreshsqlmodule` is executed on the module, the `modify_date` will be changed even though technically the code of the module has not changed. – gravidThoughts Jun 14 '18 at 17:55
how may get the Specified Database Stored Procedures only?! – Irfan Oct 10 '18 at 01:31
2

@im_one After the WHERE line, add `AND name = 'specified procedure'` where `specified procedure` is the name of the stored proc you want to find. – TylerH Dec 05 '18 at 15:19

score 49 · Answer 2 · edited Dec 06 '18 at 20:12

49

Try this for stored procedures:

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

edited Dec 06 '18 at 20:12

TylerH

20,799
66
75
101

answered Apr 07 '11 at 10:01

openshac

4,966
5
46
77

1

This should be the accepted answer because this is simple and straightforward. – Just a HK developer Jan 07 '20 at 07:03

score 16 · Answer 3 · edited Dec 06 '18 at 20:12

16

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'

edited Dec 06 '18 at 20:12

TylerH

20,799
66
75
101

answered Sep 04 '12 at 06:27

zuhaib hyder

161
1
2

Arun Prasad E S · Answer 4 · 2018-04-07T01:32:26.673

12

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

edited Apr 07 '18 at 01:32

answered Oct 20 '16 at 06:25

Arun Prasad E S

9,489
8
74
87

Curious why you would want to pull from this view instead of using sys.objects. Is this so you can get extra details on the functions/procedures? I will say I noticed in one DB the sys.objects showed a different modify date compared to the information_schema.routines alter date... – Izulien Jul 11 '22 at 14:27

score 6 · Answer 5 · answered Sep 26 '16 at 19:11

6

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

answered Sep 26 '16 at 19:11

LTA

191
3
16

Already [using sys.procedures to search for text in sprocs](https://stackoverflow.com/a/18360448/3585500) so nice I could get the dates. – ourmandave Aug 12 '22 at 14:30

score 5 · Answer 6 · edited Aug 19 '13 at 09:39

5

For SQL 2000 I would use:

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

edited Aug 19 '13 at 09:39

Satpal

132,252
13
159
168

answered Aug 19 '13 at 09:20

Simon

51
1
1

What about 2008..? Isn't that the question..? – NREZ Aug 19 '13 at 09:45
@NREZ queries on sysobjects are supported on 2008 so this will work – Paul Feb 02 '16 at 10:30
refdate is not the last modified date. Check the date difference between sys.object and sysobjects. – access_granted Jun 19 '18 at 01:29

Mohammad Reza Shahrestani · Answer 7 · 2019-10-14T11:33:14.627

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

score 1 · Answer 8 · edited May 01 '17 at 15:54

1

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

edited May 01 '17 at 15:54

Renats Stozkovs

2,549
10
22
26

answered May 01 '17 at 15:35

Kris K

11
1

1

May I request you to please add some more context around your answer. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post. – RBT May 01 '17 at 23:08

Aditya Jodhani · Answer 9 · 2022-11-14T12:23:06.420

0

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

edited Nov 14 '22 at 12:23

answered Nov 14 '22 at 12:22

Aditya Jodhani

31
2

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 21 '22 at 10:27

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

9 Answers9

Linked