Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

Check Validity of SQL Server Stored Procedures, Views and Functions

Rate me:
Please Sign up or sign in to vote.
4.73/5 (22 votes)
13 Feb 2006CPOL4 min read 153.1K   1.8K   42   31
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:

SQL
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:

SQL
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)


Written By
Web Developer
United Kingdom United Kingdom
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 4 Pin
Member 1315931528-Apr-17 8:48
Member 1315931528-Apr-17 8:48 
GeneralMy vote of 5 Pin
Member 109931435-Aug-14 5:32
Member 109931435-Aug-14 5:32 
QuestionGood Job Pin
borchanii11-Oct-12 13:06
borchanii11-Oct-12 13:06 
GeneralMy vote of 5 Pin
Kanasz Robert25-Sep-12 22:43
professionalKanasz Robert25-Sep-12 22:43 
GeneralMy vote of 5 Pin
mrsnipey26-Aug-12 18:37
mrsnipey26-Aug-12 18:37 
GeneralThanks - here's an equivalent in SQL only [modified] Pin
Danny Shisler16-Jan-10 6:45
Danny Shisler16-Jan-10 6:45 
GeneralRe: Thanks - here's an equivalent in SQL only Pin
MrTelly17-Jan-10 13:39
MrTelly17-Jan-10 13:39 
GeneralRe: Thanks - here's an equivalent in SQL only Pin
Member 454302215-Feb-10 4:28
Member 454302215-Feb-10 4:28 
GeneralRe: Thanks - here's an equivalent in SQL only [modified] Pin
sebwills28-Mar-12 3:35
sebwills28-Mar-12 3:35 
GeneralMore checks are possible Pin
Chuckxxx4-Dec-08 8:33
Chuckxxx4-Dec-08 8:33 
You can compile check a few more objects.
Also in sql05 you can use the sys.views which are documented.
Also in 05 you can choose not to check some of MS built in and hidden sprocs.




if (SqlServerMajorVersion(conn) < 9)
{
return "select name, OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') as quoted_ident_on, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') as ansi_nulls_on, user_name(o.uid) [schema], [type], [type] type_desc from sysobjects o where type in ('P', 'V', 'FN') and category = 0 order by Type, Name";
}
else
{
return "select name, OBJECTPROPERTY(object_id, 'ExecIsQuotedIdentOn') as quoted_ident_on, OBJECTPROPERTY(object_id, 'ExecIsAnsiNullsOn') as ansi_nulls_on, object_schema_name(o.object_id) [schema], [type], [type_desc] from sys.objects o where type in ('P', 'V', 'FN', 'IF' ,'TF', 'TR') AND object_id NOT IN (SELECT major_id FROM sys.extended_properties WHERE name = 'microsoft_database_tools_support') order by Type, Name";
}

//from Books Online
//C = CHECK constraint
//D = Default or DEFAULT constraint
//F = FOREIGN KEY constraint
//L = Log
//FN = Scalar function
//IF = Inlined table-function
//P = Stored procedure
//PK = PRIMARY KEY constraint (type is K)
//RF = Replication filter stored procedure
//S = System table
//TF = Table function
//TR = Trigger
//U = User table
//UQ = UNIQUE constraint (type is K)
//V = View
//X = Extended stored procedure
GeneralReally nice Pin
Olivier_Giulieri26-Aug-08 20:32
Olivier_Giulieri26-Aug-08 20:32 
GeneralFantastic Pin
sross9021-Aug-08 2:07
sross9021-Aug-08 2:07 
GeneralWorks well, with an exception Pin
Mike Renno25-Jul-08 8:30
Mike Renno25-Jul-08 8:30 
GeneralAwesome Pin
iffy45612-May-08 15:22
iffy45612-May-08 15:22 
GeneralThank you for putting this together - couple quick tweaks Pin
Emil Lerch22-Apr-08 12:28
Emil Lerch22-Apr-08 12:28 
GeneralRe: Thank you for putting this together - couple quick tweaks Pin
Emil Lerch2-May-08 6:10
Emil Lerch2-May-08 6:10 
GeneralRe: Thank you for putting this together - couple quick tweaks Pin
IPC200014-May-08 2:04
IPC200014-May-08 2:04 
GeneralPerfect for my needs Pin
Guy Harwood11-Jan-07 5:57
Guy Harwood11-Jan-07 5:57 
GeneralJust what I needed. Pin
Nick Hoggard15-May-06 16:50
Nick Hoggard15-May-06 16:50 
GeneralRe: Just what I needed. Pin
IPC200016-May-06 10:31
IPC200016-May-06 10:31 
GeneralNice usage Pin
Pedro J. Molina5-Mar-06 3:17
Pedro J. Molina5-Mar-06 3:17 
GeneralRe: Nice usage Pin
IPC20005-Mar-06 23:19
IPC20005-Mar-06 23:19 
GeneralInteresting side note Pin
fred h1-Mar-06 13:28
fred h1-Mar-06 13:28 
GeneralRe: Interesting side note Pin
IPC20002-Mar-06 1:33
IPC20002-Mar-06 1:33 
GeneralUse Generate Scripts Pin
w.b.smith24-Feb-06 11:24
w.b.smith24-Feb-06 11:24 

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

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