Click here to Skip to main content
11,802,076 members (62,208 online)
Click here to Skip to main content

Check Validity of SQL Server Stored Procedures, Views and Functions

, 13 Feb 2006 CPOL 94.5K 1.4K 42
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


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:


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.


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.


  • 13th February, 2006: Initial post


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


About the Author

Web Developer
United Kingdom United Kingdom
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Member 109931435-Aug-14 5:32
memberMember 109931435-Aug-14 5:32 
QuestionGood Job Pin
borchanii11-Oct-12 13:06
memberborchanii11-Oct-12 13:06 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:43
mvpKanasz Robert25-Sep-12 22:43 
GeneralMy vote of 5 Pin
mrsnipey26-Aug-12 18:37
membermrsnipey26-Aug-12 18:37 
GeneralThanks - here's an equivalent in SQL only [modified] Pin
ddblue16-Jan-10 6:45
memberddblue16-Jan-10 6:45 
Thanks for the article, it was a very handy pointer. I've just put this together in pure SQL, it doesn't include checks for ANSI nulls and quoted identifiers but they could be added quite easily.

    @Name NVARCHAR(100),
    @Type NVARCHAR(100),
    @Definition NVARCHAR(MAX),
                OBJECT_NAME(sm.object_id) AS object_name,
                CASE WHEN o.type_desc = 'SQL_STORED_PROCEDURE' THEN STUFF(sm.definition, CHARINDEX('CREATE PROC', sm.definition), 11, 'ALTER PROC')
                     WHEN o.type_desc lIKE '%FUNCTION%' THEN STUFF(sm.definition, CHARINDEX('CREATE FUNC', sm.definition), 11, 'ALTER FUNC')
                     WHEN o.TYPE = 'VIEW' THEN STUFF(sm.definition, CHARINDEX('CREATE VIEW', sm.definition), 11, 'ALTER VIEW')
                     WHEN o.TYPE = 'SQL_TRIGGER' THEN STUFF(sm.definition, CHARINDEX('CREATE TRIG', sm.definition), 11, 'ALTER TRIG')
        FROM    sys.sql_modules AS sm
                JOIN sys.objects AS o ON sm.object_id = o.OBJECT_ID
        ORDER BY 1, 2 ;
OPEN crRoutines
FETCH NEXT FROM crRoutines INTO @Schema, @Name, @Type, @Definition
        IF LEN(@Definition) > 0 
                BEGIN TRY
                    SET PARSEONLY ON ;
                    EXEC ( @Definition ) ;
                    SET PARSEONLY OFF ;
                END TRY
                BEGIN CATCH
			PRINT @Type + ': '+ @Schema + '.' + @Name	
                END CATCH		
        FETCH NEXT FROM crRoutines INTO @Schema, @Name, @Type, @Definition
CLOSE crRoutines

modified on Saturday, January 16, 2010 1:00 PM

GeneralRe: Thanks - here's an equivalent in SQL only Pin
MrTelly17-Jan-10 13:39
memberMrTelly17-Jan-10 13:39 
GeneralRe: Thanks - here's an equivalent in SQL only Pin
Member 454302215-Feb-10 4:28
memberMember 454302215-Feb-10 4:28 
GeneralRe: Thanks - here's an equivalent in SQL only [modified] Pin
sebwills28-Mar-12 3:35
membersebwills28-Mar-12 3:35 
GeneralMore checks are possible Pin
Chuckxxx4-Dec-08 8:33
memberChuckxxx4-Dec-08 8:33 
GeneralReally nice Pin
Olivier Giulieri26-Aug-08 20:32
memberOlivier Giulieri26-Aug-08 20:32 
GeneralFantastic Pin
sross9021-Aug-08 2:07
membersross9021-Aug-08 2:07 
GeneralWorks well, with an exception Pin
Mike Renno25-Jul-08 8:30
memberMike Renno25-Jul-08 8:30 
GeneralAwesome Pin
iffy45612-May-08 15:22
memberiffy45612-May-08 15:22 
GeneralThank you for putting this together - couple quick tweaks Pin
Emil Lerch22-Apr-08 12:28
memberEmil Lerch22-Apr-08 12:28 
GeneralRe: Thank you for putting this together - couple quick tweaks Pin
Emil Lerch2-May-08 6:10
memberEmil Lerch2-May-08 6:10 
GeneralRe: Thank you for putting this together - couple quick tweaks Pin
IPC200014-May-08 2:04
memberIPC200014-May-08 2:04 
GeneralPerfect for my needs Pin
Guy Harwood11-Jan-07 5:57
memberGuy Harwood11-Jan-07 5:57 
GeneralJust what I needed. Pin
Nick Hoggard15-May-06 16:50
memberNick Hoggard15-May-06 16:50 
GeneralRe: Just what I needed. Pin
IPC200016-May-06 10:31
memberIPC200016-May-06 10:31 
GeneralNice usage Pin
Pedro J. Molina5-Mar-06 3:17
memberPedro J. Molina5-Mar-06 3:17 
GeneralRe: Nice usage Pin
IPC20005-Mar-06 23:19
memberIPC20005-Mar-06 23:19 
GeneralInteresting side note Pin
fredddddddddddd1-Mar-06 13:28
memberfredddddddddddd1-Mar-06 13:28 
GeneralRe: Interesting side note Pin
IPC20002-Mar-06 1:33
memberIPC20002-Mar-06 1:33 
GeneralUse Generate Scripts Pin
w.b.smith24-Feb-06 11:24
memberw.b.smith24-Feb-06 11:24 
GeneralRe: Use Generate Scripts Pin
IPC200026-Feb-06 23:36
memberIPC200026-Feb-06 23:36 

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 | Terms of Use | Mobile
Web04 | 2.8.151002.1 | Last Updated 13 Feb 2006
Article Copyright 2006 by IPC2000
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid