Best way to validate objects (views & functions)

Scenario: You have a database which has seen a lot of amends to the underlying tables - tables/columns removed and renamed etc - and I want to discover all objects that are now invalid due to these changes.

I have read about a number of options - SET NOEXE, SET PARSEONLY, SET FMTONLY and even runing an alter within a transaction (dislike).

Is there a prefered method out there?

more ▼

asked Oct 09, 2010 at 08:25 AM in Default

avatar image

322 2 2 4

Thanks for your responses, all great advice.

The action I'm wanting to undertake is not immediate on the change, more a monitoring function that can perform a validating test post the event.

To explain further... We have a number of db's (internal and 3rd party developed) that host numerous objects to fuel reports. These objects have the potential to be broken by a number of routes (dev team or after a 3rd party release). I'm looking to implement a routine to sit over these db's that will alert me to any objects that have become invalid due to a change. I can them jump up and down, throw dummy etc!!! :)

The script will iterate over each object within the db, grab the definition and "execute" to discover if it is still valid.

There seems to be a number of methods to undertake the "execute" step so I wonder if anyone has experience of this and could comment on which is more appropriate?

(also, as I'm new to this forum was I correct to post this second piece here or should it have gone under the "answer your own question"?)

Oct 10, 2010 at 12:53 PM philnolan

I use SET FMTONLY to do this, but only out of habit and because it works. I've never used SET PARSEONLY because MSDN says it can't be used in a stored procedure or a trigger. I've never heard of NOEXE

Oct 11, 2010 at 01:48 AM Phil Factor
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

This is something I've been struggling with too. As I understand it, SET FMTONLY etc, and rebuilding a test database from a build script can only point out what won't execute, but it would be nice to be able to check what's missing (or added) at the column level first that is going to cause a problem (i.e. referenced from within a routine). One missing column could cause a number of failures to execute without telling you why, so it would be quicker to know what's missing, especially at 3am in the morning!

The problem for me is that columns aren't objects in the underlying model, so you can't walk the dependency tree to see what's gone. Whereas it is easy to check whether tables have disappeared, I'd like to do it at the column level.

more ▼

answered Oct 09, 2010 at 10:14 AM

avatar image

Phil Factor
4.2k 8 26 21

(comments are locked)
10|1200 characters needed characters left

Personally I'd lean on 3rd party tools (RedGate are the ones I have licensed currently) and work on a test copy of the database. Scripting it all out to a file location and then using that to rebuild would test whether objects have everything they need. May be a case of executing it a lot of times though. How far does sysdepends get you?

more ▼

answered Oct 09, 2010 at 08:46 AM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 09, 2010 at 08:25 AM

Seen: 1746 times

Last Updated: Oct 09, 2010 at 08:59 AM

Copyright 2018 Redgate Software. Privacy Policy