We all have created Stored Procedures in SQL Server. We keep doing regular updates on the applications using SQL Server database. The real problem comes when the application need to be made live and the database stored procedure is required to be synced with the live server. Unless there is no track of change monitored, it would be difficult while deploying the updates and upgrades in SQL SPs on to the live server. Now the question is more clear... Can I find the last modified date for a stored procedure in SQL Server?
Well, if you are using SQL Server 2000. You can get only the last modified date. The syntax for the same is
SELECT [Name], crDate
FROM sysobjects
WHERE [type] = 'p' and [Name] = '<Your_SP_Name>'
In case of SQL Server 2005, it is possible to find the last modified date for a stored procedure. The syntax for the same is
SELECT [Name], Create_Date, Modify_Date
FROM sys.objects
WHERE [type] = 'P' and [Name] = '<Your_SP_Name>'