Finding invalid objects before baselining
Published 01 February 2023
Some databases are online, but contain objects that are invalid. This could be caused by references to objects that no longer exist or objects/columns that have been renamed. This could also be caused if external dependencies aren't available in the environment you're re-creating the database on. We see this a lot with large systems have been around a long time. This means if you try to re-create the database from scratch using a script, it will error.
To fix this, we recommend you find any invalid objects and fix them in the database before creating the baseline script. You could also look into using a backup as a baseline instead of a baseline script.
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.
SQL Server - Redgate SQL Prompt
If you have Redgate SQL Prompt installed, open SSMS and run SQL Prompt > Find Invalid Objects. This will also identify invalid objects that needs to be reviewed.
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.
