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?