Stored procedures reverse engineering

Question

We're having problem with a huge number of legacy stored procedures at work. Do you guys recommend any tool the can help better understand those procedures? Some kind of reverse engineering that indentifies inter-procedure dependencies and/or procedure vs. tables dependencies. Can be a free or commercial tool.

Thanks!

Answer 1

redgate has a rather expensive product called SQL Dependency Tracker that seems to fulfill the requirements. Does anyone know of a cheaper solution?

Answer 2

I think the Red Gate Dependency Tracker mentioned by rpetrich is a decent solution, it works well and Red Gate has 30 day trial (ideally long enough for you do do your forensics).

I would also consider isolating the system and running the SQL Profiler which will show you all the SQL action on the tables. This is often a good starting point for building a sequence diagram or however you choose to document these codes. Good luck!

Answer 3

Thanks fo both answers!

Yes @tyler, we need luck :)

Answer 4

Redgate SQL Doc. the generated documentation included cross-referenced dependency information. For example, for each table, it lists views, stored procedures, triggers etc that reference that table.

Answer 5

What database are the stored procedures in? Oracle, SQL Server, something else?

Edit based on comment: Given you're using Oracle then, have a look at TOAD. I use a feature in it called the Code Roadmap, which allows you to graphically display PL/SQL interdependancies within the database. It can run in Code Only mode, showing runtime call stack dependancies, or Code Plus Data mode, where it also shows you database objects (tables, views, triggers) that are touched by your code.

(Note - I am a TOAD user, and gain no benefit from referring it)

Answer 6

The cheaper solution than 'dependency tracker' is the data dictionary table sys.sql_dependencies which from which this data can be queried from the data dictionary. Oracle has a data dictionary view with similar functionality called DBA_DEPENDENCIES (plus equivalent USER_ and ALL_ views) . Using the other data dictionary tables (sys.tables/DBA_TABLES) etc. you can generate object dependency reports.

If you're feeling particularly keen you can use a recursive query (Oracle CONNECT BY or SQL Server Common Table Expressions) to build a complete object dependency graph.

Here's an example of a recursive CTE on sys.sql_dependencies. It will return an entry for every dependency with its depth. Items can occur more than once, possibly at different depths, for every dependency relationship. I don't have a working Oracle instance to hand to build a CONNECT BY query on DBA_DEPENDENCIES so anyone with edit privileges and the time and expertise is welcome to annotate or edit this answer.

Note also with sys.sql_dependencies that you can get column references from referenced_minor_id. This could be used (for example) to determine which columns were actually used in the ETL sprocs from a staging area with copies of the DB tables from the source with more columns than are actually used.

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

I've got this to open-up to the community now. Could someone with convenient access to a running Oracle instance post a CONNECT BY recursive query here? Note that this is SQL-server specific and the question owner has since made it clear that he's using Oracle. I don't have a running Oracle instance to hand to develop and test anything.

Answer 7

This isn't real deep or thorough, but I think that if you're using MS SQL Server or Oracle (Perhaps Nigel can help with a PL-SQL sample)...Nigel is on to something . This only goes 3 dependencies deep, but could be modified to go however deep you need. It's not the prettiest thing...but it's functional...

Cheers. Jim

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

Answer 8

The single best tool for reverse engineering is by APEX. Its amazing. It can even trace into .NET assemblies and tell you where the procs are used. Its by far the deepest product of its kind. RedGate has great other tools but not in this case.