1

While developing and testing a new release I like to regularly run a test that checks that the references in some stored procedure still are valid and this without execution the SP’s (It would takes several hours to execute them.)

(Creating SP:s with "with SCHEMABINDING" is not acutely an option since other developers, developers that does not know much about the SP:s, must be able to make changes to the tables.)

Question #1: I have been thinking of using "Set NOEXEC on". It seems to work when detecting missing columns, but only as long as the statments are not in stored procedures?

This works

  SET NOEXEC ON  
  select column, MissingCol from mytable
  SET NOEXEC OFF

but neither this

  SET NOEXEC ON  
  EXEC mySP1
  SET NOEXEC OFF

nor this

  create procedure mySP2
    as
  SET NOEXEC ON  
  select column, MissingCol from mytable
  SET NOEXEC OFF
  go

  EXEC mySP2

seams to work. How can that be? Is something missing? Do you know a better way?

Question #2: Executing with NOEXEC ON does not work with missing tables or wrong table names. SQL Server’s Deferred Name Resolution Feature said to be the reason for that.

However, it seems to be possible to find missing tables (but not missing columns) with the following code:

  select
  object_name(referencing_id) as 'object making reference',
  referenced_class_desc,
  referenced_schema_name,
  referenced_entity_name as 'missing object'
  from sys.sql_expression_dependencies 
  where (select object_id from sys.objects where name = 
  [referenced_entity_name]) is NULL

Question #3: Are there some god way to find both missing tables, missing tables and perhaps also other problems in a more unified way?

UlfL
  • 59
  • 1
  • 3
  • Make a database project in Visual Studio and periodically update the model from the database. Any views or SPs that reference invalid tables will show up as errors. – Paul Abbott Jul 26 '18 at 15:47
  • Paul, that sounds like a really great suggestion, must try that tomorrow. – UlfL Jul 26 '18 at 16:11

0 Answers0