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

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

You must Sign In to use this message board.


Per page

Runs on SQL 2008 But does not catch everything
Vaccano

14:41 25 Mar '09

I ran this on a SQL 2008 Server and it caught stuff like a missing linked server, but it did not catch things that are new to SQL 2008 (like having a with when you do a HOLDLOCK). Kind of odd because Parse Only should have caught that, even in SQL 2008.
Sign In·View Thread·PermaLink	1.00/5

Connection string and SSPI Security context [modified]
C.D. Hilton

5:11 23 Sep '08

I found the answer in the app.config file. Cheers, -Chris modified on Tuesday, September 23, 2008 11:55 AM
Sign In·View Thread·PermaLink

Nice!
msmits

20:55 26 May '08

Hi there, I've just run this tool against a number of (2005) databases and it found a few issues I would not have found otherwise (they were procedures that may or may not be used . Very nice indeed. This sure beats opening each procedure, changing a character and executing it. Thanks for this tool. Cheers, Michel
Sign In·View Thread·PermaLink

Some welcome additions...
msmits

21:07 26 May '08

Hi Emil,

I'd like to suggest some (small) improvements:
- When you call it without parameters, show the syntax (not just on /? parameter).
- Document the necessary database rights.
- Document what is checked (sp's, views, functions(?), ..).
- Show the database in the output (useful when calling without database in the connection string).
- Support checking multiple databases.

Thanks again,
Michel

Re: Some welcome additions...
Emil Lerch

14:00 19 Sep '08

Makes sense - I recently had some time open up so I'll work on this in the next week or so. In the meantime, I added the ability to actually execute objects that the program thinks it's safe. The code may have issues with more rare datatypes (and possible SQL2K), but it caught a couple extra problems in our development database.
Sign In·View Thread·PermaLink

Very useful/clever
pap1964

21:35 6 May '08

Thank both writers for the nice tool. The idea is simple, clever and effective!

Just as small correction for SQL Server 2000: schema is a reserved word, so it must be quoted when used as a column name, i.e. in GetProcedureListSql

getObjSql = "select name, OBJECTPROPERTY(id, 'ExecIsQuotedIdentOn') as quoted_ident_on, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') as ansi_nulls_on, user_name(o.uid) \"schema\" from sysobjects o where type in ('P', 'V', 'FN') and category = 0";

Re: Very useful/clever
Wil Hutton

6:28 19 May '08

As a note, I am getting errors related to the schema reserved word when running this version on a SQL Server 2000 database. I downloaded the latest version just yesterday. Works like a dream on SQL Server 2005 and saved me quite a bit of work validating stored procedures.
Sign In·View Thread·PermaLink

Re: Very useful/clever
Emil Lerch

8:50 19 May '08

It was missing square brackets around the schema word. I found a SQL2k database to test on and the change has worked. I updated both the source code and exe to include the fix. A couple other things I fixed:

* help output now shows the "verbose" verbosity option that was part of the code but not shown to the user
* There was an issue with the invalid object count incorrectly considering an object as invalid if it contained no text. This is now fixed

[Message Removed]
Mojtaba Vali

21:31 2 May '08

Spam message removed
Sign In·View Thread·PermaLink

Horizontal Scrolling
Chris Meech

7:34 2 May '08

If you could re-format to make the body of the article a little narrower, that would help out most readers. Thanks. Smile

[Edit]
I just went back to check and see that it is the code section that you included. One of those lines is just a little too long, is all.
[/Edit]

Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
Donate to help Conquer Cancer[^]

Re: Horizontal Scrolling
Emil Lerch

7:42 2 May '08

You mean not everyone has big wide screen monitors? Sure - done.
Sign In·View Thread·PermaLink

Re: Horizontal Scrolling
Chris Meech

10:30 2 May '08

Thanks. That was quick. And thanks for the article. It's concise, explanatory and reads well. Chris Meech I am Canadian. [heard in a local bar] In theory there is no difference between theory and practice. In practice there is. [Yogi Berra] Donate to help Conquer Cancer[^]
Sign In·View Thread·PermaLink