Flyway baseline - invalid database objects

As a cross database tool, Flyway uses the term 'invalid objects' more broadly than some databases.

The objective of this validation check is to avoid including potentially broken objects into the database and as such the evaluation process is more inclusive.

These principally are:

• An object that is actually broken (e.g. a legacy proc that no longer works in prod)
• An object that cannot be validated (e.g. object on a linked server that is currently inaccessible)
• An object that is syntactically wrong and cannot be deployed

Because some of these states cannot reasonably be evaluated at rest, they are presented to the user for manual evaluation and easy filtering out if needed. They can then be re-evaluated at a later date.

Furthermore, some of these states are only possible in certain databases, the following section will detail some of these states.

SQL Server invalid objects are typically just the result of failed referential integrity, e.g. an object has been renamed and its dependencies haven't been updated to match.

Oracle by contrast, considers objects invalid when:

• Failed referential integrity e.g. your view selects from an object that has changed, and now it's broken
• "invalid" in the caching sense: the object has been invalidated by some other object changing
• Requires re-compiling 

This is noteworthy because without a runtime, it's not possible to evaluate if an object can successfully be recompiled.

Manual invalid object evaluation

If you wish to anticipate the results before a baselining process, you could use the following code snippets. Please allow for defining schema scope if relevant.

Oracle

SELECT
    OWNER AS "SCHEMA_NAME",
    OBJECT_NAME AS "OBJECT_NAME",
    OBJECT_TYPE AS "OBJECT_TYPE"
FROM
    ALL_OBJECTS
WHERE
    STATUS = 'INVALID';

MySQL

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'VIEW'
  AND table_rows is null
  AND table_comment like '%invalid%'

SQL Server

WITH linked_servers AS (
    SELECT name, 1 AS is_linked_server
    FROM sys.servers
    WHERE is_linked = 1
),
invalid_db_objects AS (
    SELECT
        cte.referencing_id AS invalid_object_id,
        SCHEMA_NAME(all_object.[schema_id]) AS invalid_obj_schema,
        all_object.name AS invalid_obj_name,
        all_object.[type] AS invalid_obj_type,
        'Invalid object name ''' + cte.obj_name + '''' AS custom_error_message
    FROM (
             SELECT
                 sed.referencing_id,
                 COALESCE(sed.referenced_schema_name + '.', '') + sed.referenced_entity_name AS obj_name
             FROM sys.sql_expression_dependencies sed
             WHERE sed.is_ambiguous = 0
               AND sed.referenced_id IS NULL
               AND sed.referenced_database_name IS NULL
         ) cte
             JOIN sys.objects all_object ON cte.referencing_id = all_object.[object_id]
),
ambiguous_db_objects AS (
    SELECT
        sed.referencing_id AS invalid_object_id,
        SCHEMA_NAME(o.[schema_id]) AS invalid_obj_schema,
        o.name AS invalid_obj_name,
        o.[type] AS invalid_obj_type,
        'Unable to verify ''' + COALESCE(sed.referenced_database_name + '.', '') + COALESCE(sed.referenced_schema_name + '.', '') + sed.referenced_entity_name + '''' AS custom_error_message,
        ISNULL(ls.is_linked_server, 0) AS is_linked_server
    FROM sys.sql_expression_dependencies sed
    JOIN sys.objects o ON sed.referencing_id = o.[object_id]
    LEFT JOIN linked_servers ls ON ls.name = sed.referenced_server_name
    WHERE sed.is_ambiguous = 0
      AND sed.referenced_id IS NULL
      AND sed.referenced_database_name IS NOT NULL
)
SELECT
    invalid_obj_schema AS [Invalid Object Schema],
    invalid_obj_name AS [Invalid Object Name],
    invalid_obj_type AS [Object Type],
    custom_error_message AS [Error Message],
    'Local' AS [Query Type]
FROM invalid_db_objects
UNION ALL
SELECT
    invalid_obj_schema AS [Invalid Object Schema],
    invalid_obj_name AS [Invalid Object Name],
    invalid_obj_type AS [Object Type],
    custom_error_message AS [Error Message],
    CASE WHEN is_linked_server = 1 THEN 'Linked Server' ELSE 'Cross DB' END AS [Query Type]
FROM ambiguous_db_objects;

PostgreSQL

SELECT pg_class.relname 
FROM pg_class, pg_index 
WHERE pg_index.indisvalid = false 
AND pg_index.indexrelid = pg_class.oid;