时间戳

首页 > 解决方案 > 架构更改后如何检测损坏的存储过程？

sql-server - 架构更改后如何检测损坏的存储过程？

问题描述

我有一个包含数百个表和数百个存储过程的数据库。数据库架构已更改（例如：某些表/列已重命名，某些列已被删除/添加等）。

问题是：如何在不实际执行的情况下检测受这些更改（执行时产生错误）影响的程序，因为一个一个地执行它们需要大量的手动和耗时的工作？

编辑

到目前为止，我找到的最接近的解决方案是：sys.dm_sql_referenced_entities。如果过程procedure_name被这些更改破坏，则类似的查询会SELECT * FROM sys.dm_sql_referenced_entities ('dbo.procedure_name', 'OBJECT'); 返回错误。

因此，我可以获取所有过程的名称，并在循环中为每个过程名称运行上层查询，并在其中执行某种逻辑，例如在出现错误时打印过程名称。

标签： sql-serverdatabase-administration

解决方案

您可以尝试此查询，请注意，在某些情况下，这也可能列出误报。

查询以获取invalid对象列表：

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

更多信息可以在这里找到

当您重新设计方案时，其他有用的查询可能是这个

查询以查找从其他人调用的过程：

declare @t table (procname varchar(200), called_in varchar(200))
declare @ProcName varchar(200)

declare crProcs cursor local FAST_FORWARD READ_ONLY for
         select distinct
                o.name as procedure_name
         from   sys.sql_modules m
           inner join sys.objects o ON m.object_id = o.object_id
         where o.Type = 'P'

open crProcs
fetch next from crProcs into @ProcName
while @@FETCH_STATUS = 0
begin
     insert into @t (procname, called_in)
     select @ProcName,
            o.name
     from   sys.sql_modules m
       inner join sys.objects o ON m.object_id = o.object_id
     where  m.definition Like '%' + @ProcName + '%'
     group by o.name

     fetch next from crProcs into @ProcName
end 
close crProcs
deallocate crProcs

select * 
from   @t t
where  1 < (select count(1) from @t t2 where t2.procname = t.procname )

查询以查找正在使用另一个对象的所有对象：

SELECT DISTINCT
       o.name AS Object_Name,
       o.type_desc,
       m.*
FROM   sys.sql_modules m
  INNER JOIN sys.objects o ON m.object_id = o.object_id
WHERE m.definition Like '%MyTableThatIWantToChangeTheScheme%'

推荐阅读