Tutorial - Find invalid objects before baselining
Published 01 February 2023
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
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
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.