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

• Download SqlValidator-ExeOnly - 5.07 KB
• Download SqlValidator - 30.16 KB

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