This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.

CleanCode logo
NAVIGATION: first page in sectionprevious pagefinal page in section


Descriptionvalidates syntax and object references in stored procedures
EnvironmentSQL Server 2005 or later
AuthorMichael Sorens
SinceCleanCode 1.1.02

This script evolved out of my search to find a safe, non-invasive, and fast technique for validating syntax and object (table, column) references in a collection of stored procedures. I found this StackOverflow post discussing various options. While I agree that actually executing each stored procedure will likely turn up more issues than just compiling them, one must exercise caution with the former approach. That is, you need to know that it is, in fact, safe to execute each and every stored procedure (i.e. does it erase some tables, for example?). This safety issue can be addressed by wrapping the execution in a transaction and rolling it back so no changes are permanent. Still, this approach could potentially take quite a long time depending on how much data you are manipulating. The code in the question, and one of the answers, suggested re-instantiating each stored procedure, as that action recompiles the procedure and does just such syntactic validation. But this approach is invasive--it's fine for a private test system, but could disrupt the work of other develoeprs on a heavily used test system. I came across the article Check Validity of SQL Server Stored Procedures, Views and Functions which presents a .NET solution, but it is the follow-up post at the bottom by "ddblue" that intrigued me more. This approach obtains the text of each stored procedure, converts the CREATE keyword to ALTER so that it can be compiled, then compiles the procedure. And that accurately reports any bad table and column references. The code runs, but I quickly ran into some issues because of the CREATE/ALTER conversion step, which I address here.


@Filter [ Type: nvarchar(255) Default: null ]
A string pattern to match one or more stored procedure names (used in a LIKE predicate). To include a wildcard as a literal instead of as a wildcard, enclose it in square brackets, e.g. [_] will match a literal underscore instead of the standard "any single character". Null disables the filter.
@Verbose [ Type: BIT Default: 0 ]
If turned on, the name of each examined stored procedure is reported. If turned off, only the stored procedures with problems are reported.


Unlike some approaches that suggest dropping then re-adding each stored procedure, this code is completely innocuous and non-invasive. After obtaining the definition of each stored procedure the CREATE statement is converted to an ALTER to avoid "this stored procedure already exists..." messages.

The CleanCode adaption of this script includes these enhancements/corrections:

The conversion from CREATE to ALTER in the original script looks for "CREATE" and "PROC" separated by a single space. In the real-world, there could spaces or tabs, and there could be one or more than one. I added the nested REPLACE sequence to convert all such occurrences to a single space, allowing the conversion to proceed as originally designed. (Thanks to Jeff Moden's article REPLACE Multiple Spaces with One, 2011.03.18.) Since the revised expression above needed to be used wherever the original sm.definition expression was used, I added a common table expression to avoid massive, unsightly code duplication. I also added RAISERROR calls to display a list of all procs checked or skipped; the @Verbose parameter was introduced to allow controlling the amount of output.

I have encountered the following anomalies in my use of this code (there may be others!):

Note that ValidateStoredProcs must be a script rather than a stored procedure because of the use of SET PARSEONLY, which cannot be used in functions or procedures.


Check all stored procedures, reporting problems only
     (default arguments)
Check all stored procedures, reporting all
     Change @Verbose to 1.
Check only stored procedures containing '_EAG'
     Change @Filter to '%[_]EAG%'.
Check only stored procedures beginning with 'My'
     Change @Filter to 'My%'.
Get CleanCode at Fast, secure and Free Open Source software downloads
Copyright © 2001-2013 Michael Sorens • Contact usPrivacy Policy
Usage governed by Mozilla Public License 1.1 and CleanCode Courtesy License
CleanCode -- The Website for Clean DesignRevised 2013.06.30