How to Find the Last Modified Date, Modified User of an Stored Procedure in SQL Server 2008

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

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.

Find out who modified a stored procedure

You could try looking at the last modification date. If you know who was logged in to their system at the time it can help narrow down your suspects.

Credit goes to Chris Diver: How to check date of last change in stored procedure or function in SQL server

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

The type for a function is FN rather than P for procedure. Or you can filter on the name column.

Get the list of stored procedures created and / or modified on a particular date?

You can try this query in any given SQL Server database:

SELECT 
    name,
    create_date,
    modify_date
FROM sys.procedures
WHERE create_date = '20120927'  

which lists out the name, the creation and the last modification date - unfortunately, it doesn't record who created and/or modified the stored procedure in question.

How to get Stored Procedure modification history in SQL Server?

You can use a DDL trigger to create a log of changes.

See https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

However, this would be useful only for changes that will happen after you will create the DDL trigger.

How can I track which user has last modified the stored proc in sql server 2005+?

If you're talking about "what is being used in the prod DB", rather than "in source control", then no.

You'd need SQL Server 2005 and above and DDL triggers.

How can I quickly identify most recently modified stored procedures in SQL Server

instead of using sysobjects which is not recommended anymore use sys.procedures

select name,create_date,modify_date
from sys.procedures
order by modify_date desc

you can do the where clause yourself but this will list it in order of modification date descending

Stored procedure modified time

You can use this to find the last modified date for a stored procedure:

select name, create_date, modify_date
from sys.procedures
where name = 'sp_MyStoredProcedure'

You can use this to find the last modified date for a table:

select name, create_date, modify_date 
from sys.tables
where name = 'MyTable'

To find the last modified date and other info for other objects, you can query sys.objects . http://msdn.microsoft.com/en-us/library/ms190324.aspx contains a full list of types you can search for.

select top 10 * 
from sys.objects 
where type in ('p', 'u')

Find the last time table was updated

If you're talking about last time the table was updated in terms of its structured has changed (new column added, column changed etc.) - use this query:

SELECT name, [modify_date] FROM sys.tables

If you're talking about DML operations (insert, update, delete), then you either need to persist what that DMV gives you on a regular basis, or you need to create triggers on all tables to record that "last modified" date - or check out features like Change Data Capture in SQL Server 2008 and newer.

SQL Server database last updated date time

Look in sys.objects should be enough, try this query

 select * from sys.objects
order by modify_date desc

---

Related Topics