Redgate Flyway

For SQL Server and Oracle

When you baseline your project in Flyway Desktop using a script, if we detect any objects that might be invalid, we will show you a list and you can decide which objects to filter out of the project. These objects will be excluded from the baseline script so it won't fail when you try to generate migrations or build/spin up a new database from the scripts in the repository. This should only be done for objects that truly aren't needed for development and deployment since they won't be included in the schema-model or when you generate additional migrations.

→

Hint: To select multiple objects, check the first row and then hold Shift+Click to check the last row. All the rows in between will be checked.

Scripts for Invalid Object detection

Here are the scripts we use to detect invalid objects in SQL Server and Oracle databases:.

SQL Server - Find Invalid Objects Toggle source code

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;

Oracle - Find Invalid Objects Toggle source code

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

If you have any feedback on these scripts or a script to find invalid objects in other databases, please get in touch to help us document this.

Redgate Flyway

Tutorial - Find invalid objects before baselining

For SQL Server and Oracle

Scripts for Invalid Object detection

Cookies on red-gate.com