i dont want my stored procedure to compile if a table it references does not exists.
asked
how can I turn deferred name resolution off? I don't want my stored procedures to compile if a table it references does not exist. Is it possible?
The workaround would be to run the stored procedure (inside a transaction if it's a proc which will change stuff).
I think even getting the estimated execution plan for the exec-statement for the procedure will spot the error, because then you'll get a compilation error at statement-level. But I'm not 100% sure about that, and if your stored procedure uses temporary tables, it won't work to get the estimated execution plan anyway, then your only option would be to execute the proc.
We had a question discussing workarounds previously (I don't believe you can account for temp tables or dynamic sql, so actual execution is still the only sure way to do it) - https://ask.sqlservercentral.com/questions/2700/how-to-check-invalid-objects-in-database.html
one of the accepted answers include a loop to check all procedures in a single pass
23 People are following this question.
