Finding all modules with (possibly) broken dependencies

Have you ever wondered which SQL stored procedures, functions, triggers or views might reference deleted objects? If yes, then this little script might come in handy for you. Script below will find even invalid cross-database dependencies. It was a bit tricky to solve problems which where caused by omitted schema names, but that has been done (at least I hope so).

Limitation:This won't find dependencies that are called via dynamic SQL (e.g. EXEC) or functions like OPENQUERY.

--CREATE PROCEDURE [dbo].[FindAllModulesWithMissingReferences]
--AS
/*****************************************************************************
Find all SQL modules which contain a reference to invalid objects.

Changes:
16.10.2013. (A. Grabovskis) Initial definition
******************************************************************************/
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
 SET NOCOUNT ON;
 
 -- first, we gather all references to objects
 CREATE TABLE #References (
  ReferencedBy varchar(517)
  ,ReferencedEntity varchar(517)
 )
 
 DECLARE @SQL nvarchar(max) = '
  USE [?];
 
  SELECT DB_NAME() + ''.'' 
    + SCHEMA_NAME(o.schema_id) + ''.'' 
    + o.[Name] 
    AS ReferencedBy
   ,COALESCE(d.referenced_database_name, DB_NAME()) + ''.'' 
    + COALESCE(d.referenced_schema_name, SCHEMA_NAME(ref.schema_id), SCHEMA_NAME(o.schema_id)) + ''.'' 
    + d.referenced_entity_name 
    AS ReferencedEntity
  FROM sys.sql_expression_dependencies AS d
  INNER JOIN sys.objects AS o ON o.object_id = d.referencing_id
  LEFT JOIN sys.objects AS ref ON ref.object_id = d.referenced_id
  WHERE is_ambiguous = 0
   AND referenced_server_name IS NULL';
 
 INSERT INTO #References
 EXEC sp_MSForEachDB @SQL;

 -- Now we must gather all objects that are created in each and every database
 SET @SQL = '
  USE [?];
  
  SELECT DB_NAME() + ''.'' + SCHEMA_NAME(schema_id) + ''.'' +OBJECT_NAME(object_id)
  FROM sys.objects
 ';
 
 CREATE TABLE #Modules (
  EntityPath varchar(517)
 )
 
 INSERT INTO #Modules
 EXEC sp_MSForEachDB @SQL

 --SELECT * FROM #References ORDER BY ReferencedBy
 
 --SELECT * FROM #Modules
 
 SELECT r.*
 FROM #References as r
 LEFT JOIN #Modules as m ON m.EntityPath = r.ReferencedEntity
 WHERE m.EntityPath IS NULL
 ORDER BY ReferencedBy
 
 DROP TABLE #References
 DROP TABLE #Modules
END

P.S. I do not guarantee 100% accuracy of the script, but I would be glad if you would give your feedback here in comments, if something does not work as expected (e.g. valid references are identified as invalid or vice verse).