I got a new requirement in office that I need to send the list of tables and stored procedures, I changed every day.
I was looking around and understood that in SQL SERVER 2005, INFORMATION_SCHEMA.ROUTINES and sys.tables, can give that information to me.
select [routine_name],created,last_altered from INFORMATION_SCHEMA.ROUTINES
where routine_type = 'PROCEDURE'
order by last_altered desc
select [name],create_date,modify_date from sys.tables
where [type] = 'u'
order by modify_date desc
I can now automate this in different ways now. One way will be to put this into a SSIS and send email as scheduled job.