Check Validity of SQL Server Stored Procedures, Views and Functions

• Download source files - 171.11 KB

Introduction

The other day I was tidying up our SQL database. This involved deleting some obsolete columns. Normally when I do this, I use a SQL script to search for all occurrences of the column I am deleting and then go through them one by one fixing up each stored proc. I remembered that in my small amount of Oracle experience, it was possible to tell in Oracle whether a stored proc was valid. I thought this would be a really handy feature for SQL Server. So the Db Validator was born.

Quick Start

• Download the source.
• Modify the dataconfiguration.config file in the bin/debug folder to point at your server and database.
• Run DbValidator.exe from the command-line. e.g. dbvalidator > c:\output.txt
• Examine the output. Any compilation errors will be listed together with the error details.

And that's about it. I find this saves a lot of time, and also helps give me confidence that I haven't broken some part of the database without knowing.

How It Works

When I set out to do this, someone reminded me of the "check syntax" feature in SQL Enterprise manager. If I could replicate this functionality, this would be ideal, as I didn't want to actually compile the database objects - just simulate compilation. So I pulled out SQL profiler and recorded the statements that are executed when you press the "Check Syntax" button. Interestingly what happens when you press this button is the following:

SET NOEXEC ON
GO
CREATE PROC....etc
GO
SET NOEXEC OFF
GO
SET PARSEONLY OFF
GO

I had not come across these SET commands before. Looking them up in BOL tells me what you have already guessed:

• SET NOEXEC "compiles each batch of Transact-SQL statements but does not execute them"
• SET PARSEONLY "Checks the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement"

Ok, so clearly SET NOEXEC is what I want. So why does Enterprise manager also turn SET PARSEONLY off after it has done the compilation. This I didn't understand, but I decided to replicate exactly what Enterprise manager does in any case.

So, what does our program need to do:

1. Get a list of all objects in the database that are stored procedures, functions or views
2. For each object get its definition. i.e. the Create Proc / view / function command
3. Execute the create command, but wrap the command in the SET NOEXEC / PARSEONLY bits as Enterprise manager did
4. Detect any errors raised and log them to the Console

Implementation Specifics

On the first pass of this code, I kept getting certain stored procedures that always errored. I figured out that this was due to the fact that they relied on quoted indentifiers being enabled (i.e. statements like the following are allowed: if isnull(@ErrMsg, '') <> ""). Obviously this is sloppy coding and this should really be fixed by replacing the "" with a '', but this case highlighted to me the point that all stored procs etc. are stored in the database together with their ANSI NULL and QUOTED INDENTIFIER settings. In order to have my program to work properly, I needed to figure out what these settings should be. This is what is happening in the following SQL statement:

select name, 
     OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') as quoted_ident_on, 
     OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') as ansi_nulls_on, 
     user_name(o.uid) owner 

from sysobjects o
where type in ('P', 'V', 'FN') and 

category = 0

As well as getting the object name, I am getting the QUOTED INDENTIFIER and ANSI NULL settings for the object.

You will also notice from the code that I am using the Enterprise Library data access block. This is a really useful block from the Microsoft Patterns and Practices team. If you don't have the enterprise library installed it should still work, but you will probably find that if you get any data access errors then these may get masked by an error complaining about lack of performance counters. Also note that I am using the June 2005 version of the library.

Conclusion

As you can see, this is a pretty simple application, but I hope you will find it as useful as I do. I guess a future enhancement could be a GUI that displays ticks and crosses against each object, but at the moment it serves my purposes well.

History

• 13^th February, 2006: Initial post

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)