Finding invalid objects before baselining
Published 01 February 2023
Some databases are online, but contain objects that are invalid. This means if you try to re-create the database from scratch using a script, it will error. This could be caused by references to objects that no longer exist or 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, legacy systems.
To fix this, we recommend you find any invalid objects and fix them in the database before creating the baseline script.
For SQL Server and Oracle
If you baseline your project in Flyway Desktop, 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 won't be included in 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. 
→ 
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.
