SQL Server stored procedure validity checking How can I check the validity of a stored procedure programmatically? Meaning that if no errors are found it will execute. How can I detect things like invalid tables names in "from" or "join" clauses?
It depends on how strictly you are defining "programmatically". If you mean a process that knows nothing of the proc it is evaluating, then you aren't going to get very far. In addition to the obstacles mentioned by @Aaron in his answer (i.e. Linked Servers, Dynamic SQL, OPENQUERY / OPENROWSET, deferred name resolution, etc), you also have the issue of permissions. The table names being correct doesn't help if the user cannot access them :).

An even bigger issue is the conditional execution of blocks of code. Consider the following test:

First we run it such that the conditional block is skipped.

DECLARE @RandomCondition INT,
        @Test INT;

SET @RandomCondition = 1;

IF (@RandomCondition = 2)
BEGIN
    SET @Test = 'a';
END;
SELECT @RandomCondition AS [RandomCondition];
GO


This encounters no errors and returns a result set of 1 row.

Second time around we run it such that the conditional block is entered.

DECLARE @RandomCondition INT,
        @Test INT;

SET @RandomCondition = 2;

IF (@RandomCondition = 2)
BEGIN
    SET @Test = 'a';
END;
SELECT @RandomCondition AS [RandomCondition];
GO


This encounters the following error (as expected):


  Msg 245, Level 16, State 1, Line 9
  Conversion failed when converting the varchar value 'a' to data type int.


So you really can't even loop through a list of procs and run them by passing NULL in for all input params as different values for a particular parameter might cause it to take several different, and non-overlapping, paths.

On the other hand, if you are defining "programmatically" to mean "an automated process", then there is hope.

You really need to set up integration testing where you can set up the environment (if need be), execute it with specific parameter values, and test the output and/or any expected changes to the environment. AND you can set up as many tests for the same stored procedure as is required by the nature of each stored procedure.

The tool I prefer to use for such testing is DbFit. It has been a few years since I have used it, but it was soooo easy to use. Unlike some of the other SQL testing frameworks, it is completely unobtrusive, requiring no changes to your database(s). The tests are defined in a wiki so it is easy to create and update them. You can group tests together and even use variables across several pages (such as connection string info, etc). You can also automate it via command-line.

Even if you don't use DbFit, you should still have some testing framework as anything you can test for programmatically (in a strict sense) isn't worth the time it will take to set it up; it will catch such a small percentage of problems that you will still need something else to catch the other 80% - 90%.