sql-server - 在 SQL Server 中查找损坏的对象

Question

是否有一个工具可以找到 SQL Server 中所有无法工作的对象(函数、过程、 View )，因为它们引用了不存在的对象？

Answer 1

您可能有兴趣查看以下文章:

• Michael J. Swart: Find Missing SQL Dependencies
• eggheadcafe.com: Find broken stuff

您可以按如下方式测试 Michael J. Swart 的解决方案:

CREATE PROCEDURE proc_bad AS
    SELECT col FROM nonexisting_table
GO

SELECT
    OBJECT_NAME(referencing_id) AS [this sproc or VIEW...],
    referenced_entity_name AS [... depends ON this missing entity name]
FROM 
    sys.sql_expression_dependencies
WHERE 
    is_ambiguous = 0
    AND OBJECT_ID(referenced_entity_name) IS NULL
ORDER BY 
    OBJECT_NAME(referencing_id), referenced_entity_name;

返回结果:

+------------------------+------------------------------------------+
| this sproc or VIEW...  |  ... depends ON this missing entity name |
|------------------------+------------------------------------------|
| proc_bad               |  nonexisting_table                       |
+------------------------+------------------------------------------+