Identify SQL Server Broken Dependencies

Broken dependencies happen when the relationship between two objects is broken. Prompt identification may help identify and prevent failing processes.

The following script will help you identify those broken dependencies.

SELECT
QuoteName(OBJECT_SCHEMA_NAME(referencing_id)) + '.'
+ QuoteName(OBJECT_NAME(referencing_id)) AS ProblemObject,
o.type_desc,
ISNULL(QuoteName(referenced_server_name) + '.', '')
+ ISNULL(QuoteName(referenced_database_name) + '.', '')
+ ISNULL(QuoteName(referenced_schema_name) + '.', '')
+ QuoteName(referenced_entity_name) AS MissingReferencedObject
FROM
sys.sql_expression_dependencies sed
LEFT JOIN sys.objects o
ON sed.referencing_id=o.object_id
WHERE
(is_ambiguous = 0)
AND (OBJECT_ID(ISNULL(QuoteName(referenced_server_name) + '.', '')
+ ISNULL(QuoteName(referenced_database_name) + '.', '')
+ ISNULL(QuoteName(referenced_schema_name) + '.', '')
+ QuoteName(referenced_entity_name)) IS NULL)
ORDER BY
ProblemObject,
MissingReferencedObject

The following script can be used to identify columns being referenced before making any change.

DECLARE @SchemaName sysname = 'dbo';
DECLARE @TableName sysname = 'Personnel';
DECLARE @ColumnName sysname = 'AdventureWorks2019';
SELECT
@SchemaName + '.' + @TableName AS [USED_OBJECT],
@ColumnName AS [COLUMN],
referencing.referencing_schema_name + '.' + referencing_entity_name AS USAGE_OBJECT,
CASE so.type
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default'
WHEN 'F' THEN 'FOREIGN KEY'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
END AS USAGE_OBJECTTYPE,
so.[type] AS USAGE_OBJECTTYPEID
FROM sys.dm_sql_referencing_entities
(
@SchemaName + '.' + @TableName,
'object'
) referencing
INNER JOIN sys.objects so
ON referencing.referencing_id = so.object_id
WHERE
EXISTS
(
SELECT
*
FROM
sys.dm_sql_referenced_entities
(
referencing_schema_name + '.' + referencing_entity_name,
'object'
) referenced
WHERE
referenced_entity_name = @TableName
AND
(
referenced.referenced_minor_name LIKE @ColumnName
-- referenced_minor_name is sometimes NULL
-- therefore add below condition (can introduce False Positives)
OR
(
referenced.referenced_minor_name IS NULL
AND
OBJECT_DEFINITION
(
OBJECT_ID(referencing_schema_name + '.' + referencing_entity_name)
) LIKE '%' + @ColumnName + '%'
)
)
)
ORDER BY
USAGE_OBJECTTYPE,
USAGE_OBJECT