Post-Deployment Script for finding unresolved references in database

gistfile1.txt

/*				
--------------------------------------------------------------------------------------
Stored Procedures are late-binding, so they will build even with references to non-existant objects, and will fail at runtime
This script attempts to find any missing references in a database, and can be run as a post deployment script in SSDT to
verify there are no unvalid references after a database has been deployed.
--------------------------------------------------------------------------------------
*/

DECLARE @ResultStr varchar(max) 
DECLARE @ResultTable TABLE

(
Refs varchar (MAX)
)
INSERT INTO @ResultTable
	SELECT 
	'Database object ' + [name] + 
	 CASE WHEN o.[Type]='TR' THEN  ' (' + (SELECT o3.[name] FROM sys.Objects o3 WHERE o3.object_id = o.parent_object_id) + ') ' ELSE '' END +
	' has an invalid reference to ' + ISNULL(referenced_entity_name,'NULL') + CASE WHEN referenced_database_name IS NULL THEN '' ELSE ' in database ' + referenced_database_name END + CHAR(10)
	FROM sys.sql_expression_dependencies ed
	JOIN sys.objects o ON ed.referencing_id = o.OBJECT_ID

	-- check if the item referenced in the entity exists
	WHERE ed.referenced_id IS NULL

	--double check against the list of all db objects (sometimes it says it doesn't exist and is  there)
	AND [referenced_entity_name] NOT IN (SELECT o2.[Name] FROM sys.objects o2)

	-- ignore references to deleted and and inserted in triggers, since they only exist at runtime
	AND referenced_entity_name NOT IN ('inserted','deleted')

	--  some functions like mailing only exist in the enterprise version of sql server and not on sql express
	AND (referenced_database_name NOT IN ('msdb') OR referenced_database_name IS NULL)
	
IF (SELECT COUNT(*) FROM @ResultTable) = 0 
BEGIN
	PRINT ' No Invalid references found'
END 
ELSE
BEGIN
	SET @ResultStr =' The following invalid references were detected in your database' + + CHAR(10)
	SET @ResultStr = @ResultStr + '----------------------------------------------------------------------' + CHAR(10) 
	SET @ResultStr =  @ResultStr + (SELECT '' + refs FROM @ResultTable FOR XML PATH ('') )
	PRINT @ResultStr
END