Click here to Skip to main content
Click here to Skip to main content

Check Validity of SQL Server Stored Procedures, Views and Functions

, 13 Feb 2006 CPOL
Rate this:
Please Sign up or sign in to vote.
A handy command line tool that checks whether stored procedures, views and functions in a database compile

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

If you aren't interested in the code or building the project, then the output is included in the distribution.
  • 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

  • 13th February, 2006: Initial post

License

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

Share

About the Author

IPC2000
Web Developer
United Kingdom United Kingdom
No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 109931435-Aug-14 5:32 
QuestionGood Job Pinmemberborchanii11-Oct-12 13:06 
GeneralMy vote of 5 PinmvpKanasz Robert25-Sep-12 22:43 
GeneralMy vote of 5 Pinmembermrsnipey26-Aug-12 18:37 
GeneralThanks - here's an equivalent in SQL only [modified] Pinmemberddblue16-Jan-10 6:45 
GeneralRe: Thanks - here's an equivalent in SQL only PinmemberMrTelly17-Jan-10 13:39 
GeneralRe: Thanks - here's an equivalent in SQL only PinmemberMember 454302215-Feb-10 4:28 
GeneralRe: Thanks - here's an equivalent in SQL only [modified] Pinmembersebwills28-Mar-12 3:35 
GeneralMore checks are possible PinmemberChuckxxx4-Dec-08 8:33 
GeneralReally nice PinmemberOlivier Giulieri26-Aug-08 20:32 
GeneralFantastic Pinmembersross9021-Aug-08 2:07 
GeneralWorks well, with an exception PinmemberMike Renno25-Jul-08 8:30 
GeneralAwesome Pinmemberiffy45612-May-08 15:22 
GeneralThank you for putting this together - couple quick tweaks PinmemberEmil Lerch22-Apr-08 12:28 
GeneralRe: Thank you for putting this together - couple quick tweaks PinmemberEmil Lerch2-May-08 6:10 
GeneralRe: Thank you for putting this together - couple quick tweaks PinmemberIPC200014-May-08 2:04 
GeneralPerfect for my needs PinmemberGuy Harwood11-Jan-07 5:57 
GeneralJust what I needed. PinmemberNick Hoggard15-May-06 16:50 
GeneralRe: Just what I needed. PinmemberIPC200016-May-06 10:31 
GeneralNice usage PinmemberPedro J. Molina5-Mar-06 3:17 
GeneralRe: Nice usage PinmemberIPC20005-Mar-06 23:19 
GeneralInteresting side note Pinmemberfredddddddddddd1-Mar-06 13:28 
GeneralRe: Interesting side note PinmemberIPC20002-Mar-06 1:33 
GeneralUse Generate Scripts Pinmemberw.b.smith24-Feb-06 11:24 
GeneralRe: Use Generate Scripts PinmemberIPC200026-Feb-06 23:36 
GeneralUseful tool but... Pinmemberncarey15-Feb-06 6:46 
GeneralRe: Useful tool but... PinmemberIPC200015-Feb-06 10:42 
GeneralRe: Useful tool but... PinmemberMichael McKechney2-Mar-06 16:47 
GeneralRe: Useful tool but... PinmemberIPC20002-Mar-06 22:14 
GeneralRe: Useful tool but... PinmemberIPC20003-Mar-06 3:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.141022.2 | Last Updated 13 Feb 2006
Article Copyright 2006 by IPC2000
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid