SEARCH:
NAVIGATION:
This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.
| N U T S H E L L | |
| Module | ValidateStoredProcs |
|---|---|
| Description | validates syntax and object references in stored procedures |
| Environment | SQL Server 2005 or later |
| Author | Michael Sorens |
| Since | CleanCode 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.
[_] will match a literal underscore
instead of the standard "any single character".
Null disables the filter.
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!):
CREATE/ALTER conversion failed.
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.
(default arguments)
Change @Verbose to 1.
Change @Filter to '%[_]EAG%'.
Change @Filter to 'My%'.
