Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

As we know, it is possible to compile and to have so stored procedures:

  SELECT * FROM Not_Exists_Or_Removed_Table


 FROM MyValidTable

The table Not_Exists_Or_Removed_Table is not exists or was removed (MyProc1) and column Removed_Column was removed (MyProc2).

How to detect and prevent creating so stored procedures?

share|improve this question
up vote 3 down vote accepted

According to the MSDN article - Deferred Name Resolution and Compilation only the first situation is working, second is not: "Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table."

For checking this kind of situations, I'd use a database project in Visual Studio with automatic (daily) deploy and verification of all warnings (maybe treat warning as errors), because these issues would be warnings, not errors.

share|improve this answer
You are right, in second case I cannot do that (create). But I can remove column and stored procedure will be invalid. – garik Mar 29 '11 at 9:16

You can prevent creating a procedure with invalid objects before executing the Create Procedure statement like that:

You will see a red line under the invalid object which underlines the error when you go with the mouse over the red line. I am not aware of other method, because the SQL will parse the procedure before executing it considering only the validity of the SQL syntax.

share|improve this answer
This helper (prompt) does not work properly. Sometimes it underlines on valid objects (a new ones), sometimes it does on invalid, so in many cases I ignore this helper. Thanks, it is better then nothing, of course. – garik Mar 29 '11 at 9:13
Yes you're right. You have to refresh the IntelliSense from Edit -> IntelliSense -> Refresh Local Cache or pres CTRL+SHIT+R. Then you will have the new tables created validated and only the invalid tables will have the red line. – yrushka Mar 29 '11 at 10:29
+1 thanks for shortcut! – garik Mar 29 '11 at 11:01

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.