存储过程反向工程

Question

我们在工作中遇到了大量的遗留存储过程问题。你们推荐使用哪些工具可以帮助更好地理解这些过程？一种逆向工程，可以识别过程之间的依赖关系和/或过程与表之间的依赖关系。可以是免费或商业工具。

谢谢！

- Marcio Aguiar

Answer 1

比“依赖跟踪器”更便宜的解决方案是数据字典表sys.sql_dependencies，从中可以查询这些数据。Oracle有一个类似功能的数据字典视图DBA_DEPENDENCIES（以及等效的USER_和ALL_视图）。使用其他数据字典表（如sys.tables/DBA_TABLES）等，您可以生成对象依赖关系报告。如果您感到特别热衷，可以使用递归查询（Oracle CONNECT BY或SQL Server公共表达式）构建完整的对象依赖关系图。

这里是对sys.sql_dependencies的递归CTE的示例。它将返回每个依赖项及其深度的条目。项目可能会出现多次，可能在不同的深度上，以满足每个依赖关系的要求。我手头没有可用的Oracle实例来构建DBA_DEPENDENCIES的CONNECT BY查询，因此欢迎具有编辑权限、时间和专业知识的任何人注释或编辑此答案。

还要注意，在sys.sql_dependencies中可以通过referenced_minor_id获取列引用。例如，可以使用此方法确定从源代码中带有更多列的数据库表的临时区域中的ETL sproc实际使用了哪些列。

with dep_cte as (
select o2.object_id  as parent_id
      ,o2.name       as parent_name
      ,o1.object_id  as child_id
      ,o1.name       as child_name
      ,d.referenced_minor_id
      ,1 as hierarchy_level
  from sys.sql_dependencies d
  join sys.objects o1
    on o1.object_id = d.referenced_major_id
  join sys.objects o2
    on o2.object_id = d.object_id
 where d.referenced_minor_id in (0,1)
   and not exists
       (select 1
          from sys.sql_dependencies d2
         where d2.referenced_major_id = d.object_id)

union all

select o2.object_id  as parent_id
      ,o2.name       as parent_name
      ,o1.object_id  as child_id
      ,o1.name       as child_name
      ,d.referenced_minor_id
      ,d2.hierarchy_level + 1 as hierarchy_level
  from sys.sql_dependencies d
  join sys.objects o1
    on o1.object_id = d.referenced_major_id
  join sys.objects o2
    on o2.object_id = d.object_id
  join dep_cte d2
    on d.object_id = d2.child_id
 where d.referenced_minor_id in (0,1)
)

select *
  from dep_cte
 order by hierarchy_level

现在我需要向社区开放这个问题。有没有方便访问正在运行的Oracle实例的人可以在这里发布一个CONNECT BY递归查询？请注意，这是特定于SQL Server的问题，问题所有者已经明确表示他正在使用Oracle。我手头没有运行中的Oracle实例来开发和测试任何东西。

- Nigel Campbell

---

不知道那个。但我认为它不太用户友好。我会看一下的。 - Marcio Aguiar

Answer 2

红门公司开发了一款名为SQL Dependency Tracker的相对昂贵的产品，可以满足需求。您可以访问此链接了解更多信息。

- rpetrich

Answer 3

我认为rpetrich提到的Red Gate依赖项跟踪器是一个不错的解决方案，它运行良好，而且Red Gate有30天的试用期（理想情况下足够你进行取证）。

我还建议隔离系统并运行SQL Profiler，它将显示表上的所有SQL操作。这通常是构建序列图或记录这些代码的其他方式的良好起点。祝你好运！

- tyler

Answer 4

虽然不是非常深入或全面，但我认为如果您使用的是MS SQL Server或Oracle（也许Nigel可以提供PL-SQL示例）......那么Nigel是正确的。 这只适用于三个依赖项深度，但可以修改为任何所需深度。它并不是最美观的东西......但它是功能性的...

select 
    so.name + case when so.xtype='P' then ' (Stored Proc)' when so.xtype='U' then ' (Table)' when so.xtype='V' then ' (View)' else ' (Unknown)' end as EntityName, 
    so2.name + case when so2.xtype='P' then ' (Stored Proc)' when so2.xtype='U' then ' (Table)' when so2.xtype='V' then ' (View)' else ' (Unknown)' end as FirstDependancy,
    so3.name + case when so3.xtype='P' then ' (Stored Proc)' when so3.xtype='U' then ' (Table)' when so3.xtype='V' then ' (View)' else ' (Unknown)' end as SecondDependancy,
    so4.name + case when so4.xtype='P' then ' (Stored Proc)' when so4.xtype='U' then ' (Table)' when so4.xtype='V' then ' (View)' else ' (Unknown)' end as ThirdDependancy
from 
  sysdepends sd 
    inner join sysobjects as so on sd.id=so.id 
    left join sysobjects as so2 on sd.depid=so2.id
    left join sysdepends as sd2 on so2.id=sd2.id and so2.xtype not in ('S','PK','D')
    left join sysobjects as so3 on sd2.depid=so3.id and so3.xtype not in ('S','PK','D')
    left join sysdepends as sd3 on so3.id=sd3.id and so3.xtype not in ('S','PK','D')
    left join sysobjects as so4 on sd3.depid=so4.id and so4.xtype not in ('S','PK','D')
where so.xtype = 'P' and left(so.name,2)<>'dt'
group by so.name, so2.name, so3.name, so4.name, so.xtype, so2.xtype, so3.xtype, so4.xtype

- jkinter

Answer 5

Redgate SQL Doc。生成的文档包括交叉引用的依赖信息。例如，对于每个表，它列出了引用该表的视图、存储过程、触发器等。

- JesseG

Answer 6

存储过程在哪个数据库中？Oracle、SQL Server 还是其他什么？

根据评论进行编辑： 鉴于您正在使用 Oracle，可以看一下 TOAD。我使用其中的一个功能叫做代码路线图，它允许您以图形方式显示数据库中的 PL/SQL 依赖关系。它可以运行在仅代码模式下，显示运行时调用堆栈依赖关系，或者在代码加数据模式下运行，在这种模式下，它还会显示您的代码所涉及的数据库对象（表、视图、触发器）。

（注意-我是 TOAD 用户，并没有从中获得任何好处）

- Mike McAllister

Answer 7

如何查找数据库对象的依赖链 (MS SQL Server 2000(?)+) 作者：Jacob Sebastian

每次需要部署新报表或修改现有报表时，需要知道哪些数据库对象依赖于给定的报表存储过程。有时报表非常复杂，每个存储过程可能有数十个依赖对象，每个依赖对象可能又依赖其他数十个对象。

他需要一种递归查找给定存储过程的所有依赖对象的方法。我编写了一个使用 CTE 的递归查询来实现这一点。

- user423430

Answer 8

逆向工程中最好的工具是 APEX。它非常出色。它甚至可以追踪到 .NET 程序集并告诉你过程在哪里被使用。它绝对是同类产品中最深入的一个。RedGate有其他很棒的工具，但在这种情况下不如APEX。

- Thomas Wagner