How can we help you today? How can we help you today?

Bulk validate SQL object code vs new schema

Is there an ability in any of the RedGate tools to run some sort of bulk validation for all SQL objects (views, stored procedures, functions, etc.)  - to ensure the code is still valid? The scenario is that we have a database with a bunch of custom objects that access a 3rd party application database.  We're upgrading the application and know that there are quite a few schema changes to the application tables.  We'd like to run a process that identifies what SQL objects are referring to obsolete columns, etc.

Henry Winkler
0

Comments

1 comment

Sort by
  • Jon Kirkwood
    Official comment

    Hi Henry Winkler 
    Thank you for reaching out via the Redgate forum regarding your bulk validation inquiry.
    If you’re looking to validate a large number of SQL scripts at once, SQL Prompt does include a Bulk Code Analysis feature.
     
    This lets you select a folder of SQL files and run all of SQL Prompt’s code analysis rules over them in one go. This can be run through SSMS or via a CLI process.
    It’s useful for spotting syntax issues, rule violations, and potential problems across a wide codebase without having to open each file individually.


    That said, it’s worth noting a couple of limitations for your scenario:

    • Bulk Code Analysis works on SQL files, not directly against database objects in a live database. If your custom objects exist only inside a database, you’d need to script them out first.
    • The analysis is static, so while it can catch many issues, it won’t always identify broken dependencies caused by schema changes (e.g. views/stored procedures referencing dropped or renamed columns).

    For that, you’d typically supplement with SQL Server’s system views (like sys.sql_expression_dependencies) or test recompiling objects against the upgraded schema.
     
    Bulk operations in SQL Prompt, including Bulk Code Analysis, require either a SQL Toolbelt Essentials or SQL Toolbelt license.
    You can find more details here: https://documentation.red-gate.com/sp/features-requiring-a-specific-license/bulk-operations/bulk-operations-in-ssms/bulk-code-analysis 

    Jon Kirkwood

Add comment

Please sign in to leave a comment.