Saturday, March 6, 2010

Daily Changes

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.

Related Posts by Categories



Widget by Scrapur

1 comment:

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

    http://schottsql.blogspot.com/2010/02/ddl-schema-change-auditing-on-sql.html

    ReplyDelete