首页 / 知识

关于数据库：存储过程逆向工程

2023-04-15 18:51:00

Stored procedures reverse engineering

我们在使用大量旧式存储过程时遇到了问题。你们推荐任何可以帮助您更好地理解这些程序的工具吗？某种反向工程，可识别过程间的依存关系和/或过程与表的依存关系。可以是免费或商业工具。

谢谢！

比"依赖跟踪器"便宜的解决方案是数据字典表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 proc中实际使用的列，其中源中的DB表副本比实际使用的列多。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

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实例来开发和测试任何东西。

Redgate有一个相当昂贵的产品，称为SQL Dependency Tracker，似乎可以满足要求。

我认为rpetrich提到的Red Gate Dependency Tracker是一个不错的解决方案，它运作良好，并且Red Gate有30天的试用期(理想情况下，足够您进行取证)。

我还将考虑隔离系统并运行SQL Profiler，它将向您显示表上的所有SQL操作。这通常是构建序列图的一个很好的起点，或者您选择记录这些代码。祝好运！

如何查找数据库对象的依赖关系链(MS SQL Server 2000(？)+)
雅各布·塞巴斯蒂安(Jacob Sebastian)

Every time he needs to deploy a new report or modify an existing
report, he needs to know what are the database objects that depend on
the given report stored procedure. Some times the reports are very
complex and each stored procedure might have dozens of dependent
objects and each dependent object may be depending on other dozens of
objects.

He needed a way to recursively find all the depending objects of a
given stored procedure. I wrote a recursive query using CTE to achieve
this.

这不是真正的深入或全面的知识，但是我认为，如果您使用的是MS SQL Server或Oracle(也许Nigel可以为PL-SQL示例提供帮助)... Nigel很有用。这只会涉及3个依赖项，但是可以进行修改，以使其变得更深入。这不是最漂亮的东西...但是它是功能性的...

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

存储过程在哪个数据库中？ Oracle，SQL Server，还有别的吗？

根据评论进行编辑：既然您正在使用Oracle，请查看TOAD。我在其中使用了一个称为"代码路线图"的功能，该功能可让您以图形方式显示数据库中PL / SQL的相互依赖性。它可以在"仅代码"模式下运行，以显示运行时调用堆栈的相关性，也可以在"代码加数据"模式下运行，在该模式下，它还可以向您显示代码所触及的数据库对象(表，视图，触发器)。

(注意-我是TOAD用户，引用它并没有任何好处)

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

逆向工程的最佳工具是APEX。太奇妙了。它甚至可以追溯到.NET程序集，并告诉您proc的使用位置。迄今为止，它是同类产品中最深的产品。 RedGate有很多其他工具，但在这种情况下没有。

存储过程逆向工程关于数据库工具