Archive

Archive for April, 2011

How To See Last Modified Date of Objects in SQL Server 2008

declare @day as int
set @day = 12
select * from
(SELECT [name],create_date,modify_date,’table’ type1,1 order1 FROM sys.tables
union
SELECT [name],create_date,modify_date,’view’ type1,2 order1 FROM sys.views
union
SELECT [name],create_date,modify_date,’trigger’ type1,5 order1 FROM sys.triggers
union
SELECT [name],create_date,modify_date,’sp’ type1,3 order1 FROM sys.procedures
WHERE [type] = ‘P’ AND is_ms_shipped = 0 AND [name] NOT LIKE ‘sp[_]%diagram%’
union
select [name],create_date,modify_date,’fn’ type1,4 order1 from sys.objects where type_desc like ‘%function%’)
as modify_table
where datediff(dd,modify_date,getdate())<@day
ORDER BY order1,modify_date DESC

Categories: SQL Server