I want to find which stored procedure I've created and also I want to find which stored procedure I've modified in my SQL Server on a particular date like 27 september 2012 (27/09/2012).
Is there any query which will list these procedures that are created and also modified on this date?
This question is related to
sql
sql-server
sql-server-2008
sql-server-2005
Here is the "newer school" version.
SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = N'PROCEDURE' and ROUTINE_SCHEMA = N'dbo'
and CREATED = '20120927'
SELECT name
FROM sys.objects
WHERE type = 'P'
AND (DATEDIFF(D,modify_date, GETDATE()) < 7
OR DATEDIFF(D,create_date, GETDATE()) < 7)
For SQL Server 2012:
SELECT name, modify_date, create_date, type
FROM sys.procedures
WHERE name like '%XXX%'
ORDER BY modify_date desc
SELECT * FROM sys.objects WHERE type='p' ORDER BY modify_date DESC
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
SELECT name, crdate, refdate
FROM sysobjects
WHERE type = 'P'
ORDER BY refdate desc
Source: Stackoverflow.com