SQL Server – All Objects On Instance Sorted By Date

One common question I hear whenever something begins to go wrong is, “Has anything been changed recently?” Finding recent changes can be a good place to search, depending on the issue, so here is a script to do just that.

/*
	-	Relatively light weight - runs in a couple seconds.
	-	Does not modify any objects (except the temp table made within)
	-	Displays all objects on instance sorted by modified date descending
	-	Identifies whether it was new or modified
	Author: Ben Farnsworth 
			Reference for Types: http://msdn.microsoft.com/en-us/library/ms177596.aspx
*/

CREATE TABLE #t(
	[dbName] VARCHAR(100)
	,[obj_name] VARCHAR(255)
	,[type] VARCHAR(100)
	,[create_date] DATETIME
	,[modify_date] DATETIME
	,[New or Modified] VARCHAR(100)
);
GO


sp_msforeachdb @command1 = '

USE [?]

INSERT INTO #t 
SELECT ''?'',name,
CASE 
	WHEN type = ''AF''	THEN ''Aggregate Function''
	WHEN type = ''C''		THEN ''CHECK constraint''
	WHEN type = ''D''		THEN ''Default or DEFAULT constraint''
	WHEN type = ''F''		THEN ''FOREIGN KEY Constraint''
	WHEN type = ''L''		THEN ''Log''
	WHEN type = ''FN''	THEN ''Scalar function''
	WHEN type = ''FS''	THEN ''Assembly (CLR) scalar-function''
	WHEN type = ''FT''	THEN ''Assembly (CLR) table-valued function''
	WHEN type = ''IF''	THEN ''In-lined table-function''
	WHEN type = ''IT''	THEN ''Internal table''
	WHEN type = ''P''		THEN ''Stored procedure''
	WHEN type = ''PC''	THEN ''Assembly (CLR) stored-procedure''
	WHEN type = ''PK''	THEN ''PRIMARY KEY constraint (type is K)''
	WHEN type = ''RF''	THEN ''Replication filter stored procedure''
	WHEN type = ''S''		THEN ''System table''
	WHEN type = ''SN''	THEN ''Synonym''
	WHEN type = ''SQ''	THEN ''Service queue''
	WHEN type = ''TA''	THEN ''Assembly (CLR) DML trigger''
	WHEN type = ''TF''	THEN ''Table function''
	WHEN type = ''TR''	THEN ''SQL DML Trigger''
	WHEN type = ''TT''	THEN ''Table type''
	WHEN type = ''U''		THEN ''User table''
	WHEN type = ''UQ''	THEN ''UNIQUE constraint (type is K)''
	WHEN type = ''V''		THEN ''View''
	WHEN type = ''X''		THEN ''Extended stored procedure''
END
AS [Type],
 create_date, modify_date,
CASE 
WHEN create_date = modify_date THEN
	''New''
Else
	''Modified''
END	
	AS [New or Modified]

FROM sys.objects
--WHERE type = ''P''
ORDER BY modify_date DESC


'

SELECT * FROM #t WHERE DB_ID(dbName) > 4 ORDER BY modify_date DESC

	DROP TABLE #t