Yes, every once in a while when I am deploying an application I ask myself the following question. What database objects (tables, stored procedures, functions etc..) did I modify with this release?
Ideally this is documented in the release plan but I will admit I have been known to slip every once in a while. Therefore, I am showcasing a query that can provide help. This query was written by Gordon Bell and it can be found here. It uses the sys.objects DMV that are included in SQL 2005 & 2008.
I will defiantly throw this script into my bag of tricks.
select name, modify_date, case when type_desc = 'USER_TABLE' then 'Table' when type_desc = 'SQL_STORED_PROCEDURE' then 'Stored Procedure' when type_desc in ('SQL_INLINE_TABLE_VALUED_FUNCTION', 'SQL_SCALAR_FUNCTION', 'SQL_TABLE_VALUED_FUNCTION') then 'Function' end as type_desc from sys.objects where type in ('U', 'P', 'FN', 'IF', 'TF') and is_ms_shipped = 0 order by 2 desc