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.
I'd probably be wary about relying on those dates in the system tables. I wrote up something on my blog a little while ago that may be helpful to you - it uses the new DDL triggers for SQL Server and can record quite a few things that are useful, including creates and alters as well as the login that did those actions.
ReplyDeletehttp://schottsql.blogspot.com/2010/02/ddl-schema-change-auditing-on-sql.html