MS SQL Server Object Validator



  • Does anyone know of a utility that will scan a MS SQL Server database and report all the invalid objects?  Example: I have a stored procedure and view that reference a table and column and I delete said column, I want a way to identify that the stored procedure and view are no longer valid.

    I currently use Red Gate's Dependency Tracker to identify dependencies that need to be updated when I make changes to the table. But when your database contains around 3000 objects and you're modifying one of the core table, you tend to miss a few.  



  • The closest I can think of is to run a script that gets the original text of all of the objects and ALTERs them.  This has a few problems:

    1. If an object has been renamed, it needs special handling to not set it back to its old name.
    2. If objects were created with option on like QUOTED_IDENTIFIER or ANSI_NULLS, then you have to be careful how you re-create them.
    3. It won't catch dynamic SQL (then again, nothing I know of will).
    4. It will catch column problems, but references to missing objects won't get caught.

    Deferred resolution is the biggest problem to worry about.  The best fixes are probably to create everything WITH SCHEMABINDING or to do the above ALTER process about 30 times in a row.  If you do either of these, then sp_depends will probably be accurate enough to rely on.

    We fix this problem by requiring special XML comments in the header of every object that lists what they depend on.  We then use an external tool to do dependency tracking.  We like this way, because we can list dependencies across database and even across technologies.  For example, I can tell what data layer code calls a given procedure.

     


Log in to reply