5,446,542 members and growing! (17,675 online)
Email Password   helpLost your password?
Database » Database » SQL Server     Beginner License: The BSD License

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

By Emil Lerch

Command line tool to check validity of objects in SQL Server databases
C# (C# 2.0, C#), SQL, Windows (Windows, Win2K, WinXP, Win2003, Vista), .NET (.NET, .NET 3.0, .NET 3.5, .NET 2.0), SQL Server (SQL 2000, SQL 2005, SQL Server), Visual Studio (VS2008, Visual Studio), ADO.NET

Posted: 2 May 2008
Updated: 19 May 2008
Views: 6,570
Bookmarked: 18 times
Announcements
Want a new Job?



Search    
Advanced Search
Sitemap
7 votes for this Article.
Popularity: 3.49 Rating: 4.13 out of 5
0 votes, 0.0%
1
0 votes, 0.0%
2
2 votes, 28.6%
3
2 votes, 28.6%
4
3 votes, 42.9%
5
Note: This is an unedited contribution. If this article is inappropriate, needs attention or copies someone else's work without reference then please Report This Article

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 seperate 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

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

About the Author

Emil Lerch



Location: United States United States

Other popular Database articles:

Article Top
Sign Up to vote for this article
You must Sign In to use this message board.
FAQ FAQ Noise ToleranceSearch Search Messages 
 Layout  Per page   
 Msgs 1 to 9 of 9 (Total in Forum: 9) (Refresh)FirstPrevNext
Subject  Author Date 
GeneralNice!membermsmits20:55 26 May '08  
GeneralSome welcome additions...membermsmits21:07 26 May '08  
GeneralVery useful/clevermemberpap196421:35 6 May '08  
GeneralRe: Very useful/clevermemberWil Hutton6:28 19 May '08  
GeneralRe: Very useful/clevermemberEmil Lerch8:50 19 May '08  
Generalwhich validation options are checked?memberMojtaba Vali21:31 2 May '08  
GeneralHorizontal ScrollingmemberChris Meech7:34 2 May '08  
GeneralRe: Horizontal ScrollingmemberEmil Lerch7:42 2 May '08  
GeneralRe: Horizontal ScrollingmemberChris Meech10:30 2 May '08  

General General    News News    Question Question    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

PermaLink | Privacy | Terms of Use
Last Updated: 19 May 2008
Editor:
Copyright 2008 by Emil Lerch
Everything else Copyright © CodeProject, 1999-2008
Web11 | Advertise on the Code Project