Get the List of Stored Procedures Created And/Or Modified on a Particular Date

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 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.

How to get a list of recently added stored procs from SQL Server database

This will list all of the stored procedures along with their creation and modified dates:

SELECT name, modify_date
FROM sys.objects
WHERE type = 'P'
  AND modify_date >= DATEADD(Day, -1, CONVERT(Date, GETDATE()))
ORDER BY modify_date DESC;

EDIT: Since modify_date will always be equal to or after create_date... Also note that you could just use sys.procedures as another answer mentioned.

Query to list all stored procedures

As Mike stated, the best way is to use information_schema. As long as you're not in the master database, system stored procedures won't be returned.

SELECT * 
  FROM DatabaseName.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'

If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures):

SELECT * 
  FROM [master].INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' 
   AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')

See when a stored procedure was second last modified/altered?

You could try the Schema Changes History report from SQL Server Management Studio.

Right click on database - Reports - Standard Reports - Schema Changes History

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 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

Get list of tables and stored procedures created in past one month

SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,create_date, GETDATE()) < 7

May be this could work for u

---

Related Topics