Monday 22 July 2019

How to get last updated or modified Stored Procedure/Tables/Functions,

I have found below script that's give detailed information about last altered sp/function /tables---

For Stored Procedure -- 

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo'

order by  LAST_ALTERED desc


For Functions-- 


SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'FUNCTION' and ROUTINE_SCHEMA = N'dbo'
order by  LAST_ALTERED desc


For Tables- 

SELECT name, [modify_date]  FROM sys.tables order by  [modify_date] desc

Alternate Script for  for last modified sp within few days--

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

AND DATEDIFF(D,modify_date, GETDATE())<2




I have above query will help you to identify last updated 

No comments:

Post a Comment