How to check that stored procedure is valid

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

CREATE PROC dbo.MyProc1
AS
  SELECT * FROM Not_Exists_Or_Removed_Table

or

CREATE PROC dbo.MyProc2
AS
  SELECT 
   Id
   ,Removed_Column
 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?

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.

StackExchange Database Administrators Q#1955, answer score: 3