Find Invalid Objects cannot detect nonexistent tables in trigger

a.higgins

When I look in a database for invalid objects, it cannot detect triggers that reference tables that do not exist.

Since I use triggers to maintain referential integrity, this can bite me if I ever deploy something to Production that contains a reference to a new object that still is undergoing UAT (and yes, we got a middle-of-the-night phone call after our last deploy to fix this).

Part of our release process is to run the Find Invalid Objects check in the database after all deployments, so it would be nice if that could be expanded to detect a problem like the following:

DROP TABLE IF EXISTS dbo.DummyTable
GO

CREATE TABLE dbo.DummyTable (SomeColumn INT NOT NULL)

GO
CREATE TRIGGER TR_ThisWillNotWork
   ON dbo.DummyTable
   AFTER DELETE
AS
BEGIN
    DELETE FROM InvalidObjectThatDoesNotExist WHERE 1 = 1
END
GO

After creating the table and trigger above on an empty database, Find Invalid Objects returns no results.

Alex B

Hi @a.higgins

What version of SQL Prompt are you using?  This should have been fixed in version 9.5.3.9640 (see the release notes here).

Kind regards,
Alex

a.higgins

@Alex B , thanks for the answer!

I'm running 10.1.0.13908 at the moment, and it's still displaying the behavior described. .

Probably not relevant, but oddly enough I don't see that version number on the page you linked to - it only goes up to 9.5.23

Alex B

Hi @a.higgins,

Version 9.5.3 (with build number 9640) is near the bottom from 14 May last year.  Having said that it doesn't seem to be working for me either.

I'm going to escalate this to the development team and I will update here when I have further information.

Kind regards,
Alex

Alex B

Hi @a.higgins

My apologies, but after discussing with the team and re-reading what was in the release notes, it seems I was wrong.

The fix was to find triggers that are on non-existent tables rather than triggers that include non-existent tables in their actions.  The latter isn't picked up because it doesn't throw an error during compilation of the object (where the former does) and that is the mechanism that is used by SQL Prompt when finding invalid objects.

So I'm afraid this isn't going to find what you are wanting it to find.

However there is this bit of code does find the trigger in the latter case (that references an invalid object in it's action):

SELECT
      obj_name = QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name)
  , obj_type = o.type_desc
  , d.referenced_database_name
  , d.referenced_schema_name
  , d.referenced_entity_name
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.[object_id]
WHERE d.is_ambiguous = 0
  AND d.referenced_id IS NULL
  AND d.referenced_server_name IS NULL -- ignore objects from Linked server
  AND CASE d.referenced_class -- if doesn’t exist
  WHEN 1 -- object
  THEN OBJECT_ID(
  ISNULL(QUOTENAME(d.referenced_database_name), DB_NAME()) + '.' +
 ISNULL(QUOTENAME(d.referenced_schema_name), SCHEMA_NAME()) + '.' +
 QUOTENAME(d.referenced_entity_name))
  WHEN 6 -- or user datatype
  THEN TYPE_ID(
  ISNULL(d.referenced_schema_name, SCHEMA_NAME()) + '.' + d.referenced_entity_name)
 WHEN 10 -- or XML schema
  THEN (
  SELECT 1 FROM sys.xml_schema_collections x
 WHERE x.name = d.referenced_entity_name
  AND x.[schema_id] = ISNULL(SCHEMA_ID(d.referenced_schema_name), SCHEMA_ID())
  )
  END IS NULL 

Hopefully that will help!

Kind regards,
Alex

a.higgins

@Alex B , that's incredibly helpful - thanks for the code snip, it's going to potentially save us a lot of trouble.

Is that something you think will get integrated into the product itself at some point?

Alex B

Hi @a.higgins,

Glad it is useful!

I hope that it will be added, though as of now I've let the team know but it's not currently something they're working on.

Sorry I can't be more precise!

Kind regards,
Alex