How to test for wrong table names and column names using scripts (SQL Server)

127 Views Asked by At

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?

0

There are 0 best solutions below