Beating deferred name resolution (and finding a bugs on the way)

Deferred name resolution is not my favorite SQL Server feature. And I suspect I'm not the only one. Fortunately I found a workaround. I designed a DDL trigger, which uses sys.sql_expression_dependencies. With this system view I can check for any unresolved dependencies. There are quite a lot of caveats, which make the code nontrivial. For example: I don't think anything meaningful can be done with rows having is_ambigous set to 1. That's why I skip them. 

One big caveat is that for this trigger to be useful you need to quantify all objects with schema names. This wouldn't be a big problem. If you do this in your stored procedure:

SELECT * FROM NotExistingTable

It shows as invalid in sys.sql_expression_dependencies. But, if you do this:

UPDATE TableAlias SET I = 0
FROM #t TableAlias

Even though it could be totally valid statement in the context of your code, the TableAlias will appear in sys.sql_expression_dependencies. This is totally wrong in my opinion (and I even filed a bug). This system view shouldn't show aliases. Yet there it is. You cannot realistically differentiate between those two. The code I'm working on uses schema names. For this reason I designed the trigger to ignore objects without them. But I'm sure there are databases, which completely ignore schemas.

Anyway, here is the code:

CREATE OR ALTER TRIGGER ValidateDDL ON DATABASE
WITH EXECUTE AS 'dbo'
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN

	SET NOCOUNT ON

	DECLARE @Data XML = EVENTDATA();
	DECLARE @InvalidObjects INT = 0;

	IF @Data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') IN 
		('CREATE_PROCEDURE', 'ALTER_PROCEDURE', 'CREATE_TRIGGER', 'ALTER_TRIGGER', 'CREATE_FUNCTION', 'ALTER_FUNCTION')
		OR @Data.exist('(/EVENT_INSTANCE/AlterTableActionList/Enable/Triggers/Name)') = 1
	BEGIN
		
		/*when enabling trigger we need to take its name from elsewhere*/
		DECLARE @Object NVARCHAR(128) = IIF(@Data.exist('(/EVENT_INSTANCE/AlterTableActionList/Enable/Triggers/Name)') = 1,
											@Data.value('(/EVENT_INSTANCE/AlterTableActionList/Enable/Triggers/Name)[1]', 'nvarchar(128)'),
											@Data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)'))
		DECLARE @Schema NVARCHAR(128) = @Data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'nvarchar(128)')

		SELECT @InvalidObjects = COUNT(1)
		FROM sys.sql_expression_dependencies
		WHERE OBJECT_NAME(referencing_id) = @Object
			AND OBJECT_SCHEMA_NAME(referencing_id) = @Schema
			AND referenced_id IS NULL
			/*ignore special trigger tables and aliases*/
			AND referenced_schema_name IS NOT NULL
			AND referenced_server_name IS NULL
			AND is_ambiguous = 0
			AND (referenced_database_name IS NULL
				/*check invalid database names*/
				OR referenced_database_name NOT IN (SELECT name COLLATE DATABASE_DEFAULT FROM sys.databases))

		/*check inside valid databases*/
		SELECT	referenced_schema_name SchemaName,
				referenced_entity_name ObjectName,
				referenced_database_name DatabaseName
		INTO #CrossChecks
		FROM sys.sql_expression_dependencies
		WHERE OBJECT_NAME(referencing_id) = @Object
			AND OBJECT_SCHEMA_NAME(referencing_id) = @Schema
			AND referenced_id IS NULL 
			AND referenced_database_name IN (SELECT name COLLATE DATABASE_DEFAULT FROM sys.databases)
			AND referenced_server_name IS NULL

		DECLARE @CheckSchema NVARCHAR(128),
				@CheckObject NVARCHAR(128),
				@CheckDatabase NVARCHAR(128)
		DECLARE cur_ CURSOR
		FOR
		    SELECT  *
		    FROM #CrossChecks
		
		OPEN cur_
		FETCH NEXT FROM cur_
		INTO @CheckSchema, @CheckObject, @CheckDatabase
		
		WHILE @@FETCH_STATUS = 0
		    BEGIN
				DECLARE @Result INT = 0
				DECLARE @SQL NVARCHAR(MAX) =	N'SELECT @ResultOUT = IIF(count(1) = 0, 1, 0) FROM sys.objects 
												WHERE name = ''' + @CheckObject + ''' AND OBJECT_SCHEMA_NAME(object_id) = ''' +@CheckSchema + ''''
				DECLARE @ParameterDefinition NVARCHAR(255) = N'@ResultOUT int OUTPUT'
				DECLARE @sp_executesql NVARCHAR(255) = @CheckDatabase + '.sys.sp_executesql'

				EXEC @sp_executesql @SQL, @ParameterDefinition, @ResultOUT = @Result OUTPUT

				SET @InvalidObjects = @InvalidObjects + @Result
		
				FETCH NEXT FROM cur_
				INTO @CheckSchema, @CheckObject, @CheckDatabase
		    END 
		CLOSE cur_;
		DEALLOCATE cur_;
		
		IF @InvalidObjects > 0
		BEGIN
			RAISERROR( 'The object references invalid entities', 16, 1)
			ROLLBACK
		END
		
	END

END

This trigger prevents creation of objects referencing invalid tables or databases in stored procedures, functions and triggers. It will generate error like this:

Msg 50000, Level 16, State 1, Procedure ValidateDDL, Line 80 [Batch Start Line 98]

The object references invalid entities

Msg 3609, Level 16, State 2, Procedure TestInvalidObjects, Line 1 [Batch Start Line 98]

The transaction ended in the trigger. The batch has been aborted.

Overall I'm pretty proud of this code, even though it doesn't cover all possible cases. Feel free to try it out. 

Note: While testing the trigger I found an additional bug to the one described at beginning. If you have two DDL triggers in the database, disable one of them and then try to alter it, the session will be terminated. For this reason, if you run into problems with this trigger, it's safer to just drop it, not disable.

If you're interested in the bug reproducer, you can check it here.