Query to find list of objects changed after a cut off date

Question

Hello All, good morning.

Just wanted to know if you have any query to find list of objects changed after a cut off date ?

SELECT top 20 name AS object_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc
,create_date
,modify_date
FROM sys.objects
WHERE modify_date > '24-may-2021'
ORDER BY modify_date desc;

I'm using this but its not listing SPs.
And also its listing tables whose indexes are rebuilt which needs to be excluded.
Please help if you know anything on this.

Thanks,
Vikram

Answer 1

Please make sure you look in a valid database.

Returning all the objects that have been modified in the last N days
Before you run the following query, replace <database_name> and <n_days> with valid values.

USE <database_name>;  
GO  
SELECT name AS object_name   
  ,SCHEMA_NAME(schema_id) AS schema_name  
  ,type_desc  
  ,create_date  
  ,modify_date  
FROM sys.objects  
WHERE modify_date > GETDATE() - <n_days>  
ORDER BY modify_date;  
GO

Or try:

USE <database_name>;  
GO  
select * 
from sys.procedures 
where modify_date > '24-may-2021'
order by modify_date desc

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Alas, there is no way to filter out tables where the only modification is an index rebuild, so the only choice is to filter out tables entirely with

WHERE type <> 'U'