CODEWITHSUNDEEP
sql-server-2008stored-proceduressql-server-2008-r2sql-function
Marek Kwiendacz
Marek Kwiendacz

Reputation: 9834

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?

Upvotes: 231

Views: 690303

Answers (9)

Aditya Jodhani
Aditya Jodhani

Reputation: 41

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

Upvotes: 0

Mohammad Reza Shahrestani
Mohammad Reza Shahrestani

Reputation: 1149

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

Upvotes: 4

openshac
openshac

Reputation: 5165

Try this for stored procedures:

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

Upvotes: 52

zuhaib hyder
zuhaib hyder

Reputation: 171

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'

Upvotes: 17

Chris Diver
Chris Diver

Reputation: 19812

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.

Upvotes: 475

Arun Prasad E S
Arun Prasad E S

Reputation: 10115

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

Upvotes: 14

Kris K
Kris K

Reputation: 11

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

Upvotes: 1

LTA
LTA

Reputation: 321

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

Upvotes: 9

Simon
Simon

Reputation: 61

For SQL 2000 I would use:

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

Upvotes: 6

Related Questions