Stephanie Herr

18 July 2025

Flyway code analysis – These are a few of my favorite rules

Clean, consistent SQL code isn’t just a preference. It’s a pathway to healthier applications, faster debugging, and happier teams. Whether you’re onboarding new developers or optimizing legacy systems, having a clear set of standards can make all the difference.

Flyway’s code analysis is a powerful ally in keeping your database code tidy and secure, and making sure best practices are being followed. It analyzes your SQL files using built-in and customizable rules for catching anti-patterns before they wreak havoc in production.

Here are a few of my favorite rules:

1. Rule 1: Avoid sp_ in stored procedure names
   Microsoft reserves the sp_ prefix for system stored procedures. You should not create any new stored procedures starting with sp_ in your database because it can slow performance due to unnecessary lookups in the master database.
2. Rule 2: Say no to SELECT *
   Using SELECT * might feel quick and easy, but it can lead to issues and poor performance. Schema changes could change the order columns are returned and returning everything when it’s not needed can bloat network usage.
3. Rule 3: Document new tables clearly
   Every new table has a purpose and that purpose can be clearly documented in its MS_Description extended property. This rule makes sure that all new tables have a description to better understand its purpose. New team members and future you will appreciate this.

More than just favorites

These three rules are my personal favorites, but they’re just the tip of the iceberg. Flyway Enterprise ships with a built-in integration to SQLFluff, an open-source SQL linter with over 60 out-of-the-box rules that check for best practices, deprecated syntax, good formatting, consistent keyword casing, proper aliases, and more.

My examples above are SQL Server focused, but SQLFluff works with over 20 database systems including SQL Server, Oracle, PostgreSQL, MySQL, Snowflake, MariaDB, Databricks and more making it easy to enforce clean code and best practices for all these database systems.

On top of that, Flyway Enterprise includes over 14 exclusive Redgate-authored rules that elevate enterprise-grade code quality and security checking for things, such as:

• Data is not being dropped
• New users or roles are not being added
• Permissions are not being changed
• New tables aren’t being created without a primary key

And because no team’s standards are one-size-fits-all, Flyway gives users the power to write their own custom rules freeing teams up for more valuable work.

Running rules as part of your CI process makes all the difference

Embedding these rules into an automated Continuous Integration (CI) process transforms them from “nice-to-haves” into enforced. Here’s how:

• Catch Mistakes Early: Code violations can be flagged before they ever leave your developer’s laptop. That means fewer rollbacks, fewer late-night fixes, and less time manually sifting through migration scripts.
• Enforce Standards Consistently: Every pull request runs through the same gatekeeper—no favoritism, no slip-throughs. Whether you’re refactoring legacy code or adding new features, the same rules apply across the board.
• Boost Collaboration: With a clear set of enforced guidelines, teams spend less time debating style and more time solving meaningful problems.
• Accelerate Delivery: Cleaner code upfront means smoother downstream testing and deployment. Fewer surprises, fewer regressions, and a faster path to production.

Summary

Flyway makes rule enforcement easy and can:

• Ensure best practices are being followed
• Analyze SQL scripts before they hit production.
• Integrate checks with CI/CD pipelines for automated validation.
• Customize and write your own rules to free up more of your time.

Whether you’re working on legacy applications or building brand new systems, Flyway’s code analysis is your secret weapon in standardizing excellence across your database development processes. Get in touch with us to learn more about integrating these rules and automating your database deployments with Flyway.