Click here to Skip to main content
11,705,753 members (57,253 online)
Click here to Skip to main content

Check Validity of SQL Server Stored Procedures/Views/Functions (updated)

, 19 Sep 2008 BSD 46.8K 618 40
Rate this:
Please Sign up or sign in to vote.
Command line tool to check validity of objects in SQL Server databases

Introduction

This is an updated version of the code posted by IPC2000. The following changes have been made:

  • References to the enterprise library have been removed, allowing for more flexibility in specifying the connection string. Connection strings and verbosity can be specified on the command line.
  • Output is more "spreadsheet friendly", using tab delimited output that can be copy/pasted into Excel
  • Support for SQL 2005 has been introduced. Separation of user from schema in SQL 2005 requires a slightly different SQL statement.
  • Validation logic has been moved to a separate class which allows for referencing/use in another project. This is especially handy for inclusion as an NUnit test.

Background

Please refer to this article for more background on the validation. This is simply a polished version of that piece.

Using the Code

The console executable can be run specifying a /c:<connection string> argument as well as a /v:<verbosity> argument. Verbosity values are:

  • Quiet: Designed for spreadsheets, a tab delimited list of invalid objects
  • Normal (default): Includes some description of what's going on. Version of SQL Server detected and a summary of objects processed and object failures
  • Verbose: Code will list every object processed as it's processing it 

Update

A new switch, /x, has been added to ask the program to actually run any procedure, function, view it thinks is safe.  If CREATE, DROP, INSERT, UPDATE, DELETE, or EXEC/EXECUTE appears in the text of the object it will move on, otherwise, it will add a dummy value for each parameter it finds and execute the object.  Since the logic isn't perfect (stored procs can call functions that do bad things), this is turned off by default, but enabling it should generally be safe if the system was coded using best practices.  This feature has limited testing behind it - please comment below if you find issues and I'll take a look when I can.

You can also reference the executable directly into NUnit and run the corresponding method. Here's an example C# test method:

[TestFixture]
public class DatabaseValidation
{
    [Test]
    public void CheckForDatabaseCompilationProblems()
    {
	Assert.IsTrue(
	DatabaseValidator.Validator.DatabaseIsValid(
	ConfigurationManager.ConnectionStrings
	[MyConnectionString].ConnectionString,
	DatabaseValidator.Verbosity.Normal),
	"Invalid objects found in the database - Run SqlValidator");
    } 
} 

History

  • 2008-05-02: Initial post

License

This article, along with any associated source code and files, is licensed under The BSD License

Share

About the Author

Emil Lerch
United States United States
No Biography provided

You may also be interested in...

Comments and Discussions

 
QuestionChanges from previous version Pin
tomvincent16-Nov-11 9:23
membertomvincent16-Nov-11 9:23 
GeneralTriggers Pin
ps_eric29-Nov-10 12:26
memberps_eric29-Nov-10 12:26 
GeneralRe: Triggers Pin
Emil Lerch5-Mar-11 17:37
memberEmil Lerch5-Mar-11 17:37 
GeneralRuns on SQL 2008 But does not catch everything Pin
Vaccano25-Mar-09 13:41
memberVaccano25-Mar-09 13:41 
GeneralConnection string and SSPI Security context [modified] Pin
C.D. Hilton23-Sep-08 4:11
memberC.D. Hilton23-Sep-08 4:11 
GeneralNice! Pin
msmits26-May-08 19:55
membermsmits26-May-08 19:55 
GeneralSome welcome additions... Pin
msmits26-May-08 20:07
membermsmits26-May-08 20:07 
GeneralRe: Some welcome additions... Pin
Emil Lerch19-Sep-08 13:00
memberEmil Lerch19-Sep-08 13:00 
GeneralVery useful/clever Pin
pap19646-May-08 20:35
memberpap19646-May-08 20:35 
GeneralRe: Very useful/clever Pin
Wil Hutton19-May-08 5:28
memberWil Hutton19-May-08 5:28 
GeneralRe: Very useful/clever Pin
Emil Lerch19-May-08 7:50
memberEmil Lerch19-May-08 7:50 
General[Message Removed] Pin
Mojtaba Vali2-May-08 20:31
memberMojtaba Vali2-May-08 20:31 
GeneralHorizontal Scrolling Pin
Chris Meech2-May-08 6:34
memberChris Meech2-May-08 6:34 
GeneralRe: Horizontal Scrolling Pin
Emil Lerch2-May-08 6:42
memberEmil Lerch2-May-08 6:42 
GeneralRe: Horizontal Scrolling Pin
Chris Meech2-May-08 9:30
memberChris Meech2-May-08 9:30 

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.150819.1 | Last Updated 19 Sep 2008
Article Copyright 2008 by Emil Lerch
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid